Oracle SQL实现行拆分生成平衡分录并标记异常行

表结构与测试数据 #

现有Oracle数据库表XXTB_JE_TXN，其创建语句和测试数据插入语句如下：

CREATE TABLE XXTB_JE_TXN 
( "ENTITY" VARCHAR2(10 BYTE), 
"JE_HEADER_ID" VARCHAR2(1000 BYTE), 
"NET_CR" NUMBER, 
"NET_DR" NUMBER
);

Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('401','10101',0,30);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('302','10101',0,20);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('402','10101',0,50);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('301','10101',100,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('402','10102',50,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('301','10102',0,100);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('401','10102',30,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('302','10102',20,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('102','10103',0,400.44);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('101','10103',992.57,325.17);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('201','10103',0,266.96);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('102','10105',62.5,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('201','10105',0,17291);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('101','10105',17228.5,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('204','10104',200,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('101','10104',0,200);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('301','10106',70,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('302','10106',30,0);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('401','10106',0,60);
Insert into XXTB_JE_TXN (ENTITY,JE_HEADER_ID,NET_CR,NET_DR) values ('402','10106',0,40);

需求说明 #

• 拆分表中行数据，生成借贷平衡的分录
• 针对JE_HEADER_ID=10106的记录，因实体间NET_CR与NET_DR无明确关联，需标记为REJECT

原SQL存在的问题 #

此前@Serg提供的SQL可处理部分场景，但未覆盖JE_HEADER_ID=10104这类一对一借贷匹配的情况，补充完整数据后需要调整实现。

原SQL代码如下：

with t as (
select ENTITY,JE_HEADER_ID, greatest(0, NET_CR-NET_DR)NET_CR, greatest(0, NET_DR-NET_CR) NET_DR
  , sum(greatest(0, NET_CR-NET_DR)) over(partition by JE_HEADER_ID) tot
from XXTB_JE_TXN 
)
select a.*
from t
cross join lateral (
  select t.ENTITY,t.JE_HEADER_ID,t2.NET_CR,t2.NET_DR
  from t t2
  where t2.JE_HEADER_ID = t.JE_HEADER_ID and t2.tot != t2.NET_CR and t2.tot != t2.NET_DR
) a
where t.tot = t.NET_CR or t.tot = t.NET_DR
  
union all
  
select ENTITY,JE_HEADER_ID,NET_DR,NET_CR
from t
where t.tot != t.NET_CR and t.tot != t.NET_DR
order by 2, 1;

优化后的Oracle SQL实现 #

以下SQL可覆盖所有需求场景，包括一对一匹配、一对多拆分以及指定凭证标记：

WITH t AS (
    SELECT 
        ENTITY,
        JE_HEADER_ID,
        GREATEST(0, NET_CR - NET_DR) AS NET_CR,
        GREATEST(0, NET_DR - NET_CR) AS NET_DR,
        SUM(GREATEST(0, NET_CR - NET_DR)) OVER(PARTITION BY JE_HEADER_ID) AS total_cr,
        SUM(GREATEST(0, NET_DR - NET_CR)) OVER(PARTITION BY JE_HEADER_ID) AS total_dr
    FROM XXTB_JE_TXN
),
-- 标记需要拒绝的凭证
reject_records AS (
    SELECT 
        ENTITY,
        JE_HEADER_ID,
        NET_CR,
        NET_DR,
        'REJECT' AS STATUS
    FROM t
    WHERE JE_HEADER_ID = '10106'
),
-- 处理一对一匹配的凭证（总借贷等于单个实体的借贷）
one_to_one AS (
    SELECT 
        t1.ENTITY,
        t1.JE_HEADER_ID,
        t1.NET_CR,
        t1.NET_DR,
        'VALID' AS STATUS
    FROM t t1
    JOIN t t2 ON t1.JE_HEADER_ID = t2.JE_HEADER_ID 
        AND t1.NET_CR = t2.NET_DR 
        AND t1.NET_DR = t2.NET_CR
    WHERE t1.JE_HEADER_ID != '10106'
    GROUP BY t1.ENTITY, t1.JE_HEADER_ID, t1.NET_CR, t1.NET_DR
),
-- 处理一对多的凭证（单个实体承担总借贷，其他实体拆分对应金额）
one_to_many AS (
    SELECT 
        t_main.ENTITY,
        t_main.JE_HEADER_ID,
        t_split.NET_DR AS NET_CR,
        t_split.NET_CR AS NET_DR,
        'VALID' AS STATUS
    FROM t t_main
    JOIN t t_split ON t_main.JE_HEADER_ID = t_split.JE_HEADER_ID
    WHERE t_main.JE_HEADER_ID != '10106'
        AND (t_main.NET_CR = t_main.total_cr OR t_main.NET_DR = t_main.total_dr)
        AND t_split.NET_CR != t_split.total_cr AND t_split.NET_DR != t_split.total_dr
)
-- 合并所有结果
SELECT * FROM reject_records
UNION ALL
SELECT * FROM one_to_one
UNION ALL
SELECT * FROM one_to_many
ORDER BY JE_HEADER_ID, ENTITY;

逻辑说明 #

1. CTE预处理：统一计算每行的实际净借贷金额，同时统计每个凭证的总借方和总贷方金额，确保凭证本身的平衡性。
2. 拒绝标记：单独筛选JE_HEADER_ID=10106的记录，添加REJECT状态标记。
3. 一对一匹配处理：匹配借贷金额完全对应的实体对，生成有效分录。
4. 一对多拆分处理：找到承担总借贷的实体，将其与其他拆分实体逐一配对，生成借贷平衡的分录。
5. 结果合并：将所有场景的结果合并，按凭证ID和实体排序输出。

内容的提问来源于stack exchange，提问作者rish