（大众金融）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;

查询结果如下：