跨越折扣分摊计算俩次月结之间的差额；
1.需求描述：由于本月可以修改历史成交单，在本月做月结的时候会将历史修改过成交单的月份重新做月结，现需求要求本次月结完后折扣与前一次月结折扣之间的差额 ；分摊到明细 ；
2.思路说明：
>1.首先找出做过跨越修改的成交单，及修改日期为当月 成交日期不为当月的记录；
>2.去分摊明细表中做对比 ；
1 SELECT B.DEALNO, 2 B.DEALDATE, 3 B.CREATETIME + 0, 4 B.PRODUCTCODE, 5 B.DEALCURRENCY, 6 B.OUTCURRENCY, 7 B.MEMBERID, 8 B.BSFLAG, 9 B.DISCOUNT, 10 B.DISCOUNTCNY, 11 B.DISCOUNTUSD, 12 B.DISCOUNTAMOUNT, 13 LAG(B.DISCOUNTAMOUNT, 1, 0) OVER(PARTITION BY B.DEALNO, B.BSFLAG /*,b.status*/ ORDER BY B.ID DESC) LAGAMOUNT, 14 B.DISCOUNTAMOUNTCNY, 15 B.DISCOUNTAMOUNTUSD, 16 B.DISCOUNTTAX, 17 B.DISCOUNTTAXCNY, 18 B.DISCOUNTTAXUSD, 19 B.STATUS, 20 B.RNUM 21 --,LAG(b.discount,1,b.discount) OVER (PARTITION BY b.dealno,b.bsflag/*,b.status*/ ORDER BY b.createtime DESC ) lag_discount 22 , 23 B.DISCOUNTAMOUNT - LAG(B.DISCOUNTAMOUNT, 1, 0) OVER(PARTITION BY B.DEALNO, B.BSFLAG /*,b.status*/ ORDER BY B.ID DESC) LAG_DISCOUNTAMOUNT 24 FROM (SELECT B.ID, 25 B.DEALNO, 26 B.DEALDATE, 27 B.CREATETIME CREATETIME, 28 B.PRODUCTCODE, 29 B.DEALCURRENCY, 30 B.OUTCURRENCY, 31 B.MEMBERID, 32 B.BSFLAG, 33 B.DISCOUNT, 34 B.DISCOUNTCNY, 35 B.DISCOUNTUSD, 36 B.DISCOUNTAMOUNT, 37 B.DISCOUNTAMOUNTCNY, 38 B.DISCOUNTAMOUNTUSD, 39 B.DISCOUNTTAX, 40 B.DISCOUNTTAXCNY, 41 B.DISCOUNTTAXUSD, 42 B.STATUS, 43 ROW_NUMBER() OVER(PARTITION BY B.DEALNO, B.BSFLAG, B.STATUS ORDER BY B.ID DESC) RNUM 44 45 FROM BASE_MEMBER_DEALER_DISCOUNT B 46 WHERE B.DEALNO IN (SELECT M.NO 47 FROM HIS_PRODUCT_DEAL_MAIN M 48 WHERE TO_CHAR(M.MODIFYTIME, 'yyyymm') = '201709' 49 AND TO_CHAR(M.DEALDATE, 'yyyymm') <> '201709' 50 AND M.STATE = 9)) B 51 WHERE RNUM < 3
第二版
1 WITH A1 AS 2 ---A1找出跨月修改的数据；37跳数据 3 (SELECT M.NO A1NO 4 FROM HIS_PRODUCT_DEAL_MAIN M 5 WHERE TO_CHAR(M.MODIFYTIME, 'yyyymm') = '201709' 6 AND TO_CHAR(M.DEALDATE, 'yyyymm') <> '201709' 7 AND M.STATE = 9 8 9 ), 10 A2 AS 11 --根据跨越修改的数据找到分摊数据 ； 12 (SELECT * 13 FROM (SELECT B.DEALNO A2NO, 14 B.PRODUCTCODE A2CODE, 15 B.BSFLAG B2BSFLAG, 16 B.DISCOUNTAMOUNT A2AMOUNT, 17 B.STATUS B2STATUS, 18 B.ISWRITEOFF, 19 ROW_NUMBER() OVER(PARTITION BY B.DEALNO, B.BSFLAG ORDER BY B.ID DESC) RN 20 FROM A1 A 21 LEFT JOIN BASE_MEMBER_DEALER_DISCOUNT B 22 ON A.A1NO = B.DEALNO 23 WHERE B.STATUS = 0 /* AND b.iswriteoff=0 AND EXISTS (SELECT 1 FROM A1 A WHERE B.DEALNO = A.A1NO) */ 24 ) 25 WHERE RN = 1) 26 27 -- FROM A1 A , BASE_MEMBER_DEALER_DISCOUNT B 28 -- WHERE A.A1NO=B.DEALNO AND B.STATUS = 0 /* AND b.iswriteoff=0 AND EXISTS (SELECT 1 FROM A1 A WHERE B.DEALNO = A.A1NO) */ ) WHERE rn=1 ) 29 SELECT * FROM A2; --45跳数据 应该为37*2=74条 ； 30 31 , A3 AS 32 --找到无效数据中最大id的那条数据 ； 33 ( 34 SELECT S.DEALNO A3NO, 35 S.PRODUCTCODE A3CODE, 36 S.DISCOUNTAMOUNT A3AMOUNT, 37 S.BSFLAG B3BSFLAG, 38 ROW_NUMBER() OVER(PARTITION BY S.PRODUCTCODE, S.DEALNO, S.BSFLAG ORDER BY S.CREATETIME DESC) RN 39 FROM BASE_MEMBER_DEALER_DISCOUNT S 40 WHERE S.STATUS = 1), A4 AS (SELECT AA2.A2NO, 41 AA2.A2CODE, 42 AA2.B2BSFLAG, 43 AA2.A2AMOUNT, 44 AA3.A3AMOUNT, 45 AA2.A2AMOUNT - AA3.A3AMOUNT AS CHAE 46 FROM A2 AA2 47 LEFT JOIN A3 AA3 48 ON AA2.A2NO = AA3.A3NO 49 AND AA2.A2CODE = AA3.A3CODE 50 AND AA2.B2BSFLAG = AA3.B3BSFLAG 51 WHERE AA3.RN = 1) 52 SELECT * FROM A4;
