一段SQL

2023-02-09 07:39:45

如何将会计分录流水合并成会计分录，环境oracle 11g，代码如下：

表：

CREATE TABLE "DEMO_VCH"

("SET_NO" VARCHAR2(45 BYTE),

"SET_ID" NUMBER,

"AP_CODE" VARCHAR2(24 BYTE),

"AP_DIRECTION" VARCHAR2(1 BYTE),

"CUR_CODE" VARCHAR2(3 BYTE),

"TX_AMT" NUMBER(24,4)

) ;

COMMENT ON COLUMN DEMO_VCH.SET_NO is '会计分录套号';

COMMENT ON COLUMN DEMO_VCH.SET_ID is '会计分录序号';

COMMENT ON COLUMN DEMO_VCH.AP_CODE is '科目码';

COMMENT ON COLUMN DEMO_VCH.AP_DIRECTION is '接待方向(D:借;C:贷)';

COMMENT ON COLUMN DEMO_VCH.CUR_CODE is '币别';

COMMENT ON COLUMN DEMO_VCH.TX_AMT is '金额';

查询：

WITH

    VCH AS

    (

        SELECT

            T.*,

            SUM(

            CASE

                WHEN AP_DIRECTION='D'

                AND AP_D2!       ='D'

                THEN 1

                ELSE 0

            END)OVER(PARTITION BY SET_NO ORDER BY SET_ID) RN --分段求和

        FROM

            (

                SELECT

                    T.*,

                    LAG(AP_DIRECTION,1,'X')OVER(PARTITION BY SET_NO ORDER BY SET_ID) AP_D2 --上一行的值

                FROM

                    DEMO_VCH T

            )

            T

    )

    ,

    D AS

    (

        SELECT * FROM VCH WHERE AP_DIRECTION='D'

    )

    ,

    C AS

    (

        SELECT * FROM VCH WHERE AP_DIRECTION='C'

    )

SELECT

    D.SET_NO,

    D.RN AS SET_ID,

    D.AP_CODE      AS D_APCODE,

    D.AP_DIRECTION AS D_DIRECTION,

    D.CUR_CODE     AS D_CUR,

    D.TX_AMT       AS D_AMT,

    C.AP_CODE      AS C_APCODE,

    C.AP_DIRECTION AS C_DIRECTION,

    C.CUR_CODE     AS C_CUR,

    C.TX_AMT       AS C_AMT

FROM

    D

FULL JOIN C

ON

    D.SET_NO=C.SET_NO

AND D.RN    =C.RN;

数据：

SET DEFINE OFF;

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',3,'M001','C','CNY',118802.5);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',2,'M002','D','CNY',-118802.5);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',0,'U001','D','CNY',-118802.5);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',1,'U002','C','CNY',118802.5);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',8,'P001','C','CNY',95.04);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',6,'M002','C','CNY',118707.46);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',4,'M001','D','CNY',-118802.5);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',1,'U002','C','CNY',39500);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',9,'P002','C','CNY',40);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',0,'U001','D','CNY',-39500);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',6,'M002','C','CNY',39389.07);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',4,'M001','D','CNY',-39479.07);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',3,'M001','C','CNY',39479.07);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',8,'P001','C','CNY',50);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',2,'M002','D','CNY',-39479.07);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',10,'U002','C','USD',87192.6);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',9,'U001','D','USD',-87192.6);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',4,'M002','D','USD',-46.47);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',8,'P003','C','USD',4.91);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',7,'X001','D','USD',-4.91);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',1,'M001','C','USD',87146.13);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',2,'M001','D','USD',-87146.13);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',0,'M002','D','USD',-87146.13);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',6,'Y001','C','USD',87192.6);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',4,'M002','D','USD',-46.47);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',2,'M001','D','USD',-87519.03);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',1,'M001','C','USD',87519.03);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',0,'M002','D','USD',-87519.03);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',10,'U002','C','USD',87565.5);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',9,'U001','D','USD',-87565.5);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',8,'P003','C','USD',4.93);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',7,'X001','D','USD',-4.93);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',6,'Y001','C','USD',87565.5);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',6,'Y001','C','USD',86684.4);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',1,'M001','C','USD',86637.93);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',4,'M002','D','USD',-46.47);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',0,'M002','D','USD',-86637.93);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',10,'U002','C','USD',86684.4);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',8,'P003','C','USD',4.88);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',7,'X001','D','USD',-4.88);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',2,'M001','D','USD',-86637.93);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',9,'U001','D','USD',-86684.4);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',1,'M001','C','USD',42823.4);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',4,'M002','D','USD',-202);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',10,'U002','C','USD',43025.4);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',9,'U001','D','USD',-43025.4);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',0,'M002','D','USD',-42823.4);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',2,'M001','D','USD',-42823.4);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',8,'P003','C','USD',2.44);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',6,'Y001','C','USD',43025.4);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',7,'X001','D','USD',-2.44);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',1,'U004','C','CNY',60000);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',3,'M002','D','CNY',-199.33);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',5,'P004','C','CNY',199.33);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',0,'U003','D','CNY',-60000);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',2,'M002','C','CNY',60000);

Insert into DEMO_VCH (SET_NO,SET_ID,AP_CODE,AP_DIRECTION,CUR_CODE,TX_AMT) values ('',1,'M002','D','CNY',-60000);

commit;

效果：

码农公寓

相关文章