需求：

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 201806102 22 201806103 33 201806101 11.11 201806112 22.22 201806113 33.33 201806111 11.21 201806122 22.32 201806123 33.33 201806121 11.44 201806132 22.44 201806133 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 A1LEFT 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.232 0.22 0.1 0.123 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.232 0.22 0.1 0.123 0.33 0 0.11