MySQL 两次JOIN同一张Orders表后SUM()计算结果错误如何修复

问题根因 #

你的判断是正确的，错误核心是两次左连接Orders表产生了笛卡尔积：当一个客户作为发件人有N条订单、作为收件人有M条订单时，连接后的临时表会生成N*M条重复记录，SUM统计时就会重复累加金额。比如客户Alex（ID=1）作为发件人有3条订单，作为收件人有1条订单，连接后生成3条重复的收件记录，所以收件金额被错误计算为300*3=900，和你得到的错误结果完全匹配。
另外你写的SQL还有一处笔误：第二次关联的表别名是o2，关联条件里写的t2.receiver_client_id会直接报字段不存在错误，需要先修正这个笔误。

修复方案 #

方案1：先聚合再关联（推荐，性能更稳定） #

先分别统计所有客户的总发送金额、总接收金额，再和Clients表关联，从根源上避免笛卡尔积：

SELECT 
    c.id,
    c.name,
    IFNULL(sent.total, 0) AS sent_amount,
    IFNULL(received.total, 0) AS received_amount
FROM Clients c
LEFT JOIN (
    SELECT sender_client_id, SUM(price) AS total
    FROM Orders
    GROUP BY sender_client_id
) sent ON c.id = sent.sender_client_id
LEFT JOIN (
    SELECT receiver_client_id, SUM(price) AS total
    FROM Orders
    GROUP BY receiver_client_id
) received ON c.id = received.receiver_client_id
ORDER BY c.id;

方案2：条件聚合单次关联 #

只关联一次Orders表，通过条件判断分别统计收发金额：

SELECT
    c.id,
    c.name,
    SUM(CASE WHEN o.sender_client_id = c.id THEN o.price ELSE 0 END) AS sent_amount,
    SUM(CASE WHEN o.receiver_client_id = c.id THEN o.price ELSE 0 END) AS received_amount
FROM Clients c
LEFT JOIN Orders o ON c.id = o.sender_client_id OR c.id = o.receiver_client_id
GROUP BY c.id, c.name
ORDER BY c.id;

以上两种方案都可以得到你预期的统计结果。

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