如何按客户计算Sum(Dr)与Sum(Cr)并消除重复记录

问题场景 #

执行SQL计算客户Debit(Dr)和Credit(Cr)总额时，同一客户被输出多条完全重复的记录，需要按客户分组，实现每个客户一行的汇总结果，展示内容包括客户ID、客户名称、Debit总额、Credit总额及期初余额。

原SQL示例 #

SELECT 
    c.customer_id,
    c.customer_name,
    SUM(t.debit) AS debit_total,
    SUM(t.credit) AS credit_total,
    o.opening_balance
FROM 
    customers c
JOIN 
    transactions t ON c.customer_id = t.customer_id
JOIN 
    opening_balances o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, c.customer_name

原执行结果示例 #

期望输出示例 #

问题根源 #

重复记录是因为多表连接产生了笛卡尔积：如果一个客户有多条交易记录，期初余额表中该客户的单条记录会和每条交易记录匹配，即使分组后聚合结果正确，也会因为非聚合字段opening_balance未纳入分组逻辑（部分SQL宽松模式允许，但会导致重复行），最终输出重复内容。

解决方案 #

方案1：补全GROUP BY的非聚合字段 #

把SELECT中所有未用聚合函数的字段都加入GROUP BY，确保分组逻辑严谨：

SELECT 
    c.customer_id,
    c.customer_name,
    SUM(t.debit) AS debit_total,
    SUM(t.credit) AS credit_total,
    o.opening_balance
FROM 
    customers c
JOIN 
    transactions t ON c.customer_id = t.customer_id
JOIN 
    opening_balances o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, c.customer_name, o.opening_balance

方案2：先汇总交易数据再关联（推荐） #

先对交易表按客户分组计算总额，再和客户表、期初余额表关联，从根源避免笛卡尔积：

SELECT 
    c.customer_id,
    c.customer_name,
    COALESCE(t.debit_total, 0) AS debit_total,
    COALESCE(t.credit_total, 0) AS credit_total,
    o.opening_balance
FROM 
    customers c
LEFT JOIN (
    SELECT 
        customer_id,
        SUM(debit) AS debit_total,
        SUM(credit) AS credit_total
    FROM transactions
    GROUP BY customer_id
) t ON c.customer_id = t.customer_id
JOIN 
    opening_balances o ON c.customer_id = o.customer_id

这个方案还能处理客户无交易记录的情况，通过COALESCE返回0值，避免出现NULL。

方案3：临时用DISTINCT去重（不推荐） #

如果只是快速去除重复行，可以在SELECT后加DISTINCT，但这只是治标，无法解决连接逻辑的根本问题：

SELECT DISTINCT
    c.customer_id,
    c.customer_name,
    SUM(t.debit) AS debit_total,
    SUM(t.credit) AS credit_total,
    o.opening_balance
FROM 
    customers c
JOIN 
    transactions t ON c.customer_id = t.customer_id
JOIN 
    opening_balances o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, c.customer_name

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