求Oracle SQL语句：合并支付表数据，按人员计算支付金额或差额

需求说明 #

需要整合两张表的统计结果，实现：

• 仅在payments表的人员，展示该表统计的支付总额personAmountPayments
• 仅在extraPayments表的人员，展示该表统计的支付总额personAmountExtraPayments
• 同时存在于两张表的人员，展示personAmountPayments减去personAmountExtraPayments的差额
• 结果必须包含personId列

实现SQL语句 #

WITH payments_summary AS (
    SELECT 
        SUM(amount) AS personAmountPayments,
        personId
    FROM payments
    WHERE code = '023' 
      AND paiementDate = '01/04/2023' -- 若paiementDate为DATE类型，建议改为TO_DATE('01/04/2023', 'DD/MM/YYYY')
    GROUP BY personId
),
extra_payments_summary AS (
    SELECT 
        SUM(amount) AS personAmountExtraPayments,
        personId
    FROM extraPayments
    WHERE code = '23' 
      AND paiementDate = '01/04/2023' -- 若paiementDate为DATE类型，建议改为TO_DATE('01/04/2023', 'DD/MM/YYYY')
    GROUP BY personId
)
SELECT 
    COALESCE(p.personId, ep.personId) AS personId,
    CASE
        WHEN p.personId IS NOT NULL AND ep.personId IS NOT NULL 
            THEN NVL(p.personAmountPayments, 0) - NVL(ep.personAmountExtraPayments, 0)
        WHEN p.personId IS NOT NULL 
            THEN NVL(p.personAmountPayments, 0)
        ELSE NVL(ep.personAmountExtraPayments, 0)
    END AS calculated_amount
FROM payments_summary p
FULL OUTER JOIN extra_payments_summary ep
    ON p.personId = ep.personId;

关键说明 #

1. WITH子句：先分别完成两张表的分组统计，简化主查询逻辑
2. FULL OUTER JOIN：确保获取仅在单表或同时在两表的所有人员
3. COALESCE函数：统一获取非空的personId
4. CASE表达式：根据人员存在的表情况，计算对应金额；用NVL处理NULL值，避免计算时出现NULL结果
5. 日期格式提示：如果paiementDate是DATE类型，直接用字符串匹配可能存在格式冲突，建议用TO_DATE函数明确转换格式

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