基于两表联合查询获取Credit与Debt及SQL代码调试求助

1. 基于两张表联合查询获取贷方（Credit）与借方（Debt）数据 #

针对这类需求，我整理了两种常见场景的实现方案，你可以根据自己的表结构选择：

场景A：两张表分别存储借方、贷方交易记录 #

假设你有DEBT_TRANS（借方交易表）和CREDIT_TRANS（贷方交易表），两张表共享third_party、NUM_PUROR、ACCOUNT_CODE关联字段，各自有金额字段DEBT_AMOUNT和CREDIT_AMOUNT，可以用全外连接聚合计算：

SELECT 
    COALESCE(d.third_party, c.third_party) AS third_party,
    COALESCE(d.NUM_PUROR, c.NUM_PUROR) AS NUM_PUROR,
    COALESCE(d.ACCOUNT_CODE, c.ACCOUNT_CODE) AS ACCOUNT_CODE,
    SUM(COALESCE(d.DEBT_AMOUNT, 0)) AS DEBT,
    SUM(COALESCE(c.CREDIT_AMOUNT, 0)) AS CREDIT
FROM DEBT_TRANS d
FULL OUTER JOIN CREDIT_TRANS c
    ON d.third_party = c.third_party
    AND d.NUM_PUROR = c.NUM_PUROR
    AND d.ACCOUNT_CODE = c.ACCOUNT_CODE
GROUP BY 
    COALESCE(d.third_party, c.third_party),
    COALESCE(d.NUM_PUROR, c.NUM_PUROR),
    COALESCE(d.ACCOUNT_CODE, c.ACCOUNT_CODE);

场景B：单张明细表含借贷字段，需联合关联表 #

如果是像你代码里的BFS_ACCOUNT_COUVHER_ITEMS这种同时包含借方、贷方字段的明细表，可以先聚合明细数据，再和关联表（比如凭证主表）关联：

SELECT 
    a.third_party,
    a.NUM_PUROR,
    a.DTACN_COD_ACN_DTACN AS ACCOUNT_CODE,
    SUM(a.DEBT_TOTAL) AS DEBT,
    SUM(a.CREDIT_TOTAL) AS CREDIT,
    b.DOCUMENT_DATE -- 主表的凭证日期，按需添加
FROM (
    SELECT 
        third_party,
        NUM_PUROR,
        DTACN_COD_ACN_DTACN,
        SUM(AMN_DBT_ACNVI) AS DEBT_TOTAL,
        SUM(AMN_CRD_ACNVI) AS CREDIT_TOTAL
    FROM BFS_ACCOUNT_COUVHER_ITEMS
    GROUP BY third_party, NUM_PUROR, DTACN_COD_ACN_DTACN
) a
JOIN BFS_ACCOUNT_COUVHER_HEADER b
    ON a.NUM_PUROR = b.NUM_PUROR -- 假设主表与明细表通过采购编码关联
GROUP BY a.third_party, a.NUM_PUROR, a.DTACN_COD_ACN_DTACN, b.DOCUMENT_DATE;

2. 你的SQL代码异常排查与修正 #

看你贴的代码，有几个明显的语法和逻辑问题导致报错，我帮你梳理并修正：

原代码的问题点 #

1. 语法错误：字段间缺少逗号：比如DTACN_COD_ACN_DTACN后直接跟SUM(DEBT)，SQL解析器无法识别字段边界
2. 聚合规则违反：子查询使用了SUM函数，但未对third_party、NUM_PUROR、DTACN_COD_ACN_DTACN分组
3. WHERE条件不完整：TO_CHAR(..., 'YYYY') BETWEEN '1...的年份范围未写完，需要补充完整的起始/结束年份
4. 逻辑偏差：SUM(AMN_DBT_ACNVI - AMN_CRD_ACNVI)会把贷方金额从借方中扣除，这通常不是统计借贷余额的正确方式，应该分别统计两者总和

修正后的完整代码 #

SELECT 
    kv.third_party, -- THIRD PARTY CODE
    kv.NUM_PUROR, -- PURCHASE CODE
    kv.DTACN_COD_ACN_DTACN, -- ACCOUNT CODE
    SUM(kv.DEBT) AS DEBT, 
    SUM(kv.CREDIT) AS CREDIT
FROM (
    SELECT 
        ACNVI.third_party, 
        ACNVI.NUM_PUROR, 
        ACNVI.DTACN_COD_ACN_DTACN,
        SUM(ACNVI.AMN_DBT_ACNVI) AS DEBT, -- 单独统计借方总金额
        SUM(ACNVI.AMN_CRD_ACNVI) AS CREDIT -- 单独统计贷方总金额
    FROM BFS_ACCOUNT_COUVHER_ITEMS ACNVI
    -- 补充完整年份范围，示例用2023-2024，可根据需求修改
    WHERE TO_CHAR(ACNVI.ACVOH_DAT_DCM_ACVOH, 'YYYY') BETWEEN '2023' AND '2024'
    -- 必须GROUP BY所有非聚合字段
    GROUP BY ACNVI.third_party, ACNVI.NUM_PUROR, ACNVI.DTACN_COD_ACN_DTACN
) kv
-- 若子查询已完成聚合，此处GROUP BY可省略，按需调整
GROUP BY kv.third_party, kv.NUM_PUROR, kv.DTACN_COD_ACN_DTACN;

可选：拆分借贷为独立行的写法 #

如果你需要把每笔借方、贷方分别作为独立记录展示，用UNION ALL更合适：

SELECT 
    third_party,
    NUM_PUROR,
    DTACN_COD_ACN_DTACN,
    AMN_DBT_ACNVI AS DEBT,
    0 AS CREDIT
FROM BFS_ACCOUNT_COUVHER_ITEMS
WHERE TO_CHAR(ACVOH_DAT_DCM_ACVOH, 'YYYY') BETWEEN '2023' AND '2024'
UNION ALL
SELECT 
    third_party,
    NUM_PUROR,
    DTACN_COD_ACN_DTACN,
    0 AS DEBT,
    AMN_CRD_ACNVI AS CREDIT
FROM BFS_ACCOUNT_COUVHER_ITEMS
WHERE TO_CHAR(ACVOH_DAT_DCM_ACVOH, 'YYYY') BETWEEN '2023' AND '2024'
ORDER BY third_party, NUM_PUROR;

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