本文主要是介绍(大众金融)SQL server面试题(3)-客户已用额度总和,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
今天,面试了一家公司,什么也不说先来三道面试题做做,第三题。
那么,我们就开始做题吧,谁叫我们是打工人呢。
题目是这样的:
DEALER_INFO | |||||
经销商授信协议号码 | 经销商名称 | 经销商证件号 | 注册地址 | 员工人数 | 信息维护日期 |
DEALER_NUMBER | DEALER_NAME | DEALER_ID_NO | ADDRESS | STUFF_NUMBER | MODIFY_DATE |
1001001 | TEST_01 | ID_001 | BEIJING CHAOYANG | 100 | 2020-01-01 |
2001001 | TEST_01 | ID_001 | BEIJING CHAOYANG | 200 | 2020-02-01 |
1002002 | TEST_02 | ID_002 | SHANGHAI PUDONG | 1000 | 2020-03-15 |
3002002 | TEST_02 | ID_002 | SHANGHAI MINHANG | 1000 | 2020-01-20 |
2003003 | TEST_03 | ID_003 | BEIJING HAIDIAN | 50 | 2020-02-25 |
4003003 | TEST_03 | ID_003 | BEIJING DONGCHENG | 200 | 2020-01-01 |
1004004 | TEST_04 | ID_004 | SHANGHAI HUANGPU | 100 | 2020-03-01 |
CREDIT_LIMIT_INFO | ||
经销商授信协议号码 | 品牌 | 已用额度 |
DEALER_NUMBER | BRAND | UTILIZED_LIMIT |
1001001 | FAW | 1000 |
2001001 | SKODA | 1500 |
1002002 | FAW | 200 |
3002002 | AUDI | 400 |
2003003 | SVW | 500 |
4003003 | PORCHE | 50 |
1004004 | FAW | 700 |
DEALER_INFO表在每个经销商申请代理一个品牌的额度时,单独维护对应的基本信息。
CREDIT_LIMIT_INFO表维护每个授信协议的额度金额。
问:要求以相同的证件号码为唯一标识识别为一个客户,取每个客户最近一次维护的基本信息进行报送,同时取这个客户已用额度总和。
最终报文数据样式:
客户名称 | 证件号码 | 注册地址 | 员工人数 | 已用额度 |
DEALER_NAME | DEALER_ID_NO | ADDRESS | STUFF_NUMBER | UTILIZED_LIMIT |
TEST_01 | ID_001 | BEIJING CHAOYANG | 200 | 2500 |
TEST_02 | ID_002 | SHANGHAI PUDONG | 1000 | 600 |
TEST_03 | ID_003 | BEIJING HAIDIAN | 50 | 550 |
TEST_04 | ID_004 | SHANGHAI HUANGPU | 100 | 700 |
---------------------------------------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS #DEALER_INFO;
CREATE TABLE #DEALER_INFO (
DEALER_NUMBER VARCHAR(20),
DEALER_NAME VARCHAR(50),
DEALER_ID_NO VARCHAR(50),
ADDRESS VARCHAR(100),
STUFF_NUMBER INT,
MODIFY_DATE VARCHAR(10)
); INSERT INTO #DEALER_INFO(DEALER_NUMBER, DEALER_NAME, DEALER_ID_NO, ADDRESS, STUFF_NUMBER, MODIFY_DATE) VALUES ('1001001','TEST_01', 'ID_001', 'BEIJING CHAOYANG' , 100, '2020-01-01');
INSERT INTO #DEALER_INFO(DEALER_NUMBER, DEALER_NAME, DEALER_ID_NO, ADDRESS, STUFF_NUMBER, MODIFY_DATE) VALUES ('2001001','TEST_01', 'ID_001', 'BEIJING CHAOYANG' , 200, '2020-02-01');
INSERT INTO #DEALER_INFO(DEALER_NUMBER, DEALER_NAME, DEALER_ID_NO, ADDRESS, STUFF_NUMBER, MODIFY_DATE) VALUES ('1002002','TEST_02', 'ID_002', 'SHANGHAI PUDONG' , 1000, '2020-03-15');
INSERT INTO #DEALER_INFO(DEALER_NUMBER, DEALER_NAME, DEALER_ID_NO, ADDRESS, STUFF_NUMBER, MODIFY_DATE) VALUES ('3002002','TEST_02', 'ID_002', 'SHANGHAI MINHANG' , 1000, '2020-01-20');
INSERT INTO #DEALER_INFO(DEALER_NUMBER, DEALER_NAME, DEALER_ID_NO, ADDRESS, STUFF_NUMBER, MODIFY_DATE) VALUES ('2003003','TEST_03', 'ID_003', 'BEIJING HAIDIAN' , 50, '2020-02-25');
INSERT INTO #DEALER_INFO(DEALER_NUMBER, DEALER_NAME, DEALER_ID_NO, ADDRESS, STUFF_NUMBER, MODIFY_DATE) VALUES ('4003003','TEST_03', 'ID_003', 'BEIJING DONGCHENG', 200, '2020-01-01');
INSERT INTO #DEALER_INFO(DEALER_NUMBER, DEALER_NAME, DEALER_ID_NO, ADDRESS, STUFF_NUMBER, MODIFY_DATE) VALUES ('1004004','TEST_04', 'ID_004', 'SHANGHAI HUANGPU' , 100, '2020-03-01'); DROP TABLE IF EXISTS #CREDIT_LIMIT_INFO;
CREATE TABLE #CREDIT_LIMIT_INFO (
DEALER_NUMBER VARCHAR(20),
BRAND VARCHAR(50),
UTILIZED_LIMIT INT
); INSERT INTO #CREDIT_LIMIT_INFO (DEALER_NUMBER, BRAND, UTILIZED_LIMIT) VALUES ('1001001','FAW' , 1000);
INSERT INTO #CREDIT_LIMIT_INFO (DEALER_NUMBER, BRAND, UTILIZED_LIMIT) VALUES ('2001001','SKODA' , 1500);
INSERT INTO #CREDIT_LIMIT_INFO (DEALER_NUMBER, BRAND, UTILIZED_LIMIT) VALUES ('1002002','FAW' , 200 );
INSERT INTO #CREDIT_LIMIT_INFO (DEALER_NUMBER, BRAND, UTILIZED_LIMIT) VALUES ('3002002','AUDI' , 400 );
INSERT INTO #CREDIT_LIMIT_INFO (DEALER_NUMBER, BRAND, UTILIZED_LIMIT) VALUES ('2003003','SVW' , 500 );
INSERT INTO #CREDIT_LIMIT_INFO (DEALER_NUMBER, BRAND, UTILIZED_LIMIT) VALUES ('4003003','PORCHE' , 50 );
INSERT INTO #CREDIT_LIMIT_INFO (DEALER_NUMBER, BRAND, UTILIZED_LIMIT) VALUES ('1004004','FAW' , 700 ); SELECT A.DEALER_NAME, A.DEALER_ID_NO, A.ADDRESS, A.STUFF_NUMBER, B.SUM_LIMIT
FROM
(
SELECT * FROM
(
select DEALER_NAME, DEALER_ID_NO, ADDRESS, STUFF_NUMBER, MODIFY_DATE,
ROW_NUMBER() OVER(PARTITION BY DEALER_ID_NO ORDER BY MODIFY_DATE DESC) AS ROW_NUMBER
from #DEALER_INFO A
) T WHERE T.ROW_NUMBER = 1
) A
LEFT JOIN
(
select A.DEALER_NAME, SUM(B.UTILIZED_LIMIT) AS SUM_LIMIT
from #DEALER_INFO A INNER JOIN #CREDIT_LIMIT_INFO B ON A.DEALER_NUMBER = B.DEALER_NUMBER
GROUP BY A.DEALER_NAME
) B
ON A.DEALER_NAME = B.DEALER_NAME;
查询结果如下:
结语:这个题目,在百度没有搜到,可能我的搜素能力不行吧,但是我给出了答案,希望给各位一个参考,不当之处请指出。
仁者见仁智者见智。
这篇关于(大众金融)SQL server面试题(3)-客户已用额度总和的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!