SQL多表关联分组计算债务：双列分组与缺失值处理实现

思路拆解 #

• 先分别对OldDebt、RecoveredDebt按customerId+baseCurrencyId分组求和，得到两个汇总表
• 用*FULL OUTER JOIN*关联两个汇总表，确保单表存在的客户-币种组合不丢失，缺失的求和值用0填充
• 最后关联Customer表，过滤出实际存在的客户数据（若需包含无债务的客户，可调整关联方式）

完整SQL代码 #

WITH OldDebtSummary AS (
    SELECT 
        customerId,
        baseCurrencyId,
        SUM(debtAmount) AS totalOldDebt
    FROM OldDebt
    GROUP BY customerId, baseCurrencyId
),
RecoveredDebtSummary AS (
    SELECT 
        customerId,
        baseCurrencyId,
        SUM(recoveredAmount) AS totalRecoveredDebt
    FROM RecoveredDebt
    GROUP BY customerId, baseCurrencyId
)
SELECT 
    c.id AS customerId,
    COALESCE(ods.baseCurrencyId, rds.baseCurrencyId) AS baseCurrencyId,
    COALESCE(ods.totalOldDebt, 0) AS totalOldDebt,
    COALESCE(rds.totalRecoveredDebt, 0) AS totalRecoveredDebt,
    COALESCE(ods.totalOldDebt, 0) - COALESCE(rds.totalRecoveredDebt, 0) AS totalDebt
FROM Customer c
LEFT JOIN (
    SELECT 
        COALESCE(ods.customerId, rds.customerId) AS customerId,
        COALESCE(ods.baseCurrencyId, rds.baseCurrencyId) AS baseCurrencyId,
        ods.totalOldDebt,
        rds.totalRecoveredDebt
    FROM OldDebtSummary ods
    FULL OUTER JOIN RecoveredDebtSummary rds
        ON ods.customerId = rds.customerId
        AND ods.baseCurrencyId = rds.baseCurrencyId
) debtCombined ON c.id = debtCombined.customerId
-- 若需包含无任何债务记录的客户，删除下面的WHERE条件
WHERE debtCombined.customerId IS NOT NULL;

关键细节说明 #

• *COALESCE*：处理FULL OUTER JOIN产生的NULL值，将缺失的求和项替换为0，保证totalDebt计算准确
• *FULL OUTER JOIN*：覆盖仅在OldDebt或仅在RecoveredDebt存在的客户-币种组合，避免数据遗漏
• 关联Customer表时用*LEFT JOIN*：如果需要保留所有客户（哪怕没有任何债务记录），直接删除最后的WHERE条件即可

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