常州网红景点,seo招聘网,wordpress积分冻结,ps可以做网站吗今天#xff0c;面试了一家公司#xff0c;什么也不说先来三道面试题做做#xff0c;第三题。
那么#xff0c;我们就开始做题吧#xff0c;谁叫我们是打工人呢。
题目是这样的#xff1a;
DEALER_INFO经销商授信协议号码经销商名称经销商证件号注册地址员工人数信息维…今天面试了一家公司什么也不说先来三道面试题做做第三题。
那么我们就开始做题吧谁叫我们是打工人呢。
题目是这样的
DEALER_INFO经销商授信协议号码经销商名称经销商证件号注册地址员工人数信息维护日期DEALER_NUMBERDEALER_NAMEDEALER_ID_NOADDRESSSTUFF_NUMBERMODIFY_DATE1001001TEST_01ID_001BEIJING CHAOYANG1002020-01-012001001TEST_01ID_001BEIJING CHAOYANG2002020-02-011002002TEST_02ID_002SHANGHAI PUDONG10002020-03-153002002TEST_02ID_002SHANGHAI MINHANG10002020-01-202003003TEST_03ID_003BEIJING HAIDIAN502020-02-254003003TEST_03ID_003BEIJING DONGCHENG2002020-01-011004004TEST_04ID_004SHANGHAI HUANGPU1002020-03-01
CREDIT_LIMIT_INFO经销商授信协议号码品牌已用额度DEALER_NUMBERBRANDUTILIZED_LIMIT1001001FAW10002001001SKODA15001002002FAW2003002002AUDI4002003003SVW5004003003PORCHE501004004FAW700 DEALER_INFO表在每个经销商申请代理一个品牌的额度时单独维护对应的基本信息。 CREDIT_LIMIT_INFO表维护每个授信协议的额度金额。 问要求以相同的证件号码为唯一标识识别为一个客户取每个客户最近一次维护的基本信息进行报送同时取这个客户已用额度总和。 最终报文数据样式
客户名称证件号码注册地址员工人数已用额度DEALER_NAMEDEALER_ID_NOADDRESSSTUFF_NUMBERUTILIZED_LIMITTEST_01ID_001BEIJING CHAOYANG2002500TEST_02ID_002SHANGHAI PUDONG1000600TEST_03ID_003BEIJING HAIDIAN50550TEST_04ID_004SHANGHAI HUANGPU100700
---------------------------------------------------------------------------------------------------------------------------------
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; 查询结果如下 结语这个题目在百度没有搜到可能我的搜素能力不行吧但是我给出了答案希望给各位一个参考不当之处请指出。
仁者见仁智者见智。
