NC246102. 查询潜在贷款业务客户信息?
描述
示例1
输入:
drop table if exists `user_tb` ; CREATE TABLE `user_tb` ( `user_id` int(11) NOT NULL, `age` int(11) NOT NULL, `work_years` int(11) NOT NULL, `annual_income` varchar(16) NOT NULL, PRIMARY KEY (`user_id`)); INSERT INTO user_tb VALUES(101,33,10,'100000-150000'); INSERT INTO user_tb VALUES(102,31,6,'100000-150000'); INSERT INTO user_tb VALUES(103,23,1,'50000-100000'); INSERT INTO user_tb VALUES(104,43,15,'150000-200000'); INSERT INTO user_tb VALUES(105,27,2,'100000-150000'); INSERT INTO user_tb VALUES(106,35,15,'50000-100000'); INSERT INTO user_tb VALUES(107,37,15,'100000-150000'); INSERT INTO user_tb VALUES(108,41,20,'200000-250000'); drop table if exists `account_tb` ; CREATE TABLE `account_tb` ( `user_id` int(11) NOT NULL, `credit_card` char(4) NOT NULL, `credit_card_limit` int(11) NOT NULL, `loan` char(4) NOT NULL, `loan_amount` int(11) NOT NULL, PRIMARY KEY (`user_id`)); INSERT INTO account_tb VALUES(101,'是',10000,'否',0); INSERT INTO account_tb VALUES(102,'是',20000,'是',100000); INSERT INTO account_tb VALUES(103,'是',5000,'否',0); INSERT INTO account_tb VALUES(104,'否',0,'是',50000); INSERT INTO account_tb VALUES(105,'否',0,'否',0); INSERT INTO account_tb VALUES(106,'是',5000,'否',0); INSERT INTO account_tb VALUES(107,'是',10000,'否',0); INSERT INTO account_tb VALUES(108,'是',20000,'否',0); drop table if exists `bill_tb` ; CREATE TABLE `bill_tb` ( `user_id` int(11) NOT NULL, `savings_pay` int(11) NOT NULL, `credit_pay` int(11) NOT NULL, PRIMARY KEY (`user_id`)); INSERT INTO bill_tb VALUES(101,80000,50000); INSERT INTO bill_tb VALUES(102,90000,60000); INSERT INTO bill_tb VALUES(103,50000,40000); INSERT INTO bill_tb VALUES(104,90000,0); INSERT INTO bill_tb VALUES(105,100000,0); INSERT INTO bill_tb VALUES(106,60000,50000); INSERT INTO bill_tb VALUES(107,130000,40000); INSERT INTO bill_tb VALUES(108,150000,50000);
输出:
user_id|age|annual_income|credit_card_limit|ratio 106|35|50000-100000|5000|45.5% 103|23|50000-100000|5000|44.4% 101|33|100000-150000|10000|38.5%
Mysql 解法, 执行用时: 220ms, 内存消耗: 3680K, 提交时间: 2023-08-13 17:47:24
select t.user_id,t.age,t.annual_income,t.credit_card_limit,concat(round(b.credit_pay*100/(b.savings_pay+b.credit_pay), 1), '%') as ratio from (select u.user_id,u.age,u.annual_income,a.credit_card_limit from user_tb as u left join account_tb as a on u.user_id = a.user_id where a.credit_card='是' and a.loan='否') as t left join bill_tb as b on t.user_id=b.user_id order by ratio desc limit 3;