需求：
1 根据客户信息表中信息比较出相应余额。
2 表中有客户号，余额，各个时间点的分区信息，每个分区中客户号唯一。
3 客户信息中分区有10、11、12、13、14、15。
4 得到每个比上日余额信息，如下面表样。
--创建测试表 客户信息表 CREATE TABLE CUST_INFO_TEST( CUST_NO VARCHAR2(5),AUM_EDU NUMBER,DT VARCHAR(10) ) --在表中插入测试数据 SELECT * FROM CUST_INFO_TEST FOR UPDATE --测试数据如下 CUST_NO AUM_EDU DT ---------- ---------- ----------- 1 11 20180610 2 22 20180610 3 33 20180610 1 11.11 20180611 2 22.22 20180611 3 33.33 20180611 1 11.21 20180612 2 22.32 20180612 3 33.33 20180612 1 11.44 20180613 2 22.44 20180613 3 33.44 20180613 --使用表的自关联实现 SELECT A1.CUST_NO,A3.AUM_EDU - A2.AUM_EDU AS BSR_11,A4.AUM_EDU - A3.AUM_EDU AS BSR_12,A1.AUM_EDU - A4.AUM_EDU AS BSR_13 FROM CUST_INFO_TEST A1 LEFT JOIN CUST_INFO_TEST A2 ON A1.CUST_NO = A2.CUST_NO AND A2.DT = ‘20180610‘ LEFT JOIN CUST_INFO_TEST A3 ON A1.CUST_NO = A3.CUST_NO AND A3.DT = ‘20180611‘ LEFT JOIN CUST_INFO_TEST A4 ON A1.CUST_NO = A4.CUST_NO AND A4.DT = ‘20180612‘ WHERE A1.DT= ‘20180613‘; --得到如下结果 CUST_NO BSR_11 BSR_12 BSR_13 -------- ---------- ---------- ---------- 1 0.11 0.1 0.23 2 0.22 0.1 0.12 3 0.33 0 0.11 --优化 SELECT CUST_NO,SUM(CASE WHEN DT=‘20180611‘ THEN AUM_EDU ELSE 0 END - CASE WHEN DT=‘20180610‘ THEN AUM_EDU ELSE 0 END) AS BSR_11,SUM(CASE WHEN DT=‘20180612‘ THEN AUM_EDU ELSE 0 END - CASE WHEN DT=‘20180611‘ THEN AUM_EDU ELSE 0 END) AS BSR_12,SUM(CASE WHEN DT=‘20180613‘ THEN AUM_EDU ELSE 0 END - CASE WHEN DT=‘20180612‘ THEN AUM_EDU ELSE 0 END) AS BSR_13 FROM CUST_INFO_TEST WHERE DT IN (‘20180610‘,‘20180611‘,‘20180612‘,‘20180613‘) GROUP BY CUST_NO --得到结果和上面一样 CUST_NO BSR_11 BSR_12 BSR_13 -------- ---------- ---------- ---------- 1 0.11 0.1 0.23 2 0.22 0.1 0.12 3 0.33 0 0.11
