MySQL SUM() 聚合查詢陷阱：多表 JOIN 導致重複計算的正確解法

MySQL SUM() 聚合查詢陷阱：多表 JOIN 導致重複計算的正確解法

使用 SUM() 進行多表關聯查詢時，若直接 JOIN 後聚合，極易因笛卡兒積導致重複計算，得到錯誤結果。本文透過實戰案例剖析錯誤成因，提供標準解決方案。

錯誤場景重現

兩張表：account（儲值記錄）、info（消費記錄）。需求：找出「儲值成功總額 > 消費成功總額」的使用者。

❌ 錯誤寫法（直接 JOIN + GROUP BY）

SELECT a.userid,
       SUM(a.number) AS mai,
       SUM(i.total) AS hua
FROM account AS a, info AS i
WHERE a.state = 1
  AND i.state = 1
  AND a.userid = i.userid
GROUP BY a.userid
HAVING SUM(a.number) > SUM(i.total);

錯誤現象：假設某用戶儲值 4 筆、消費 3 筆。JOIN 後產生 4×3=12 列，導致：

• 儲值總額被重複計算 3 次（實際值 × 3）
• 消費總額被重複計算 4 次（實際值 × 4）

根本原因分析

JOIN 會產生笛卡兒積。一對多關聯中，「一」的一方資料會隨著「多」的一方重複。直接對 JOIN 結果聚合，等於對重複資料聚合。

✅ 正確解法：子查詢預聚合再 JOIN

SELECT a.userid, a.mai, i.hua
FROM (
    SELECT userid, SUM(number) AS mai
    FROM account
    WHERE state = 1
    GROUP BY userid
) AS a
JOIN (
    SELECT userid, SUM(total) AS hua
    FROM info
    WHERE state = 1
    GROUP BY userid
) AS i ON a.userid = i.userid
WHERE a.mai > i.hua;

核心原則

1. 先聚合，後關聯：在子查詢中各自完成 GROUP BY + SUM()，再以主鍵 JOIN
2. 避免一對多 JOIN 後直接聚合：除非你明確需要笛卡兒積語意
3. 使用 CTE (WITH 子句) 提升可讀性 (MySQL 8.0+)：

WITH recharge AS (
    SELECT userid, SUM(number) AS total FROM account WHERE state=1 GROUP BY userid
),
consume AS (
    SELECT userid, SUM(total) AS total FROM info WHERE state=1 GROUP BY userid
)
SELECT r.userid, r.total AS recharge_total, c.total AS consume_total
FROM recharge r JOIN consume c ON r.userid = c.userid
WHERE r.total > c.total;

延伸應用：條件聚合替代方案

若兩表結構相似，可考慮 UNION ALL 合併後條件聚合：

SELECT userid,
       SUM(CASE WHEN type='recharge' THEN amount END) AS recharge_total,
       SUM(CASE WHEN type='consume' THEN amount END) AS consume_total
FROM (
    SELECT userid, number AS amount, 'recharge' AS type FROM account WHERE state=1
    UNION ALL
    SELECT userid, total AS amount, 'consume' AS type FROM info WHERE state=1
) t
GROUP BY userid
HAVING recharge_total > consume_total;

掌握「先聚合後關聯」原則，徹底避開多表聚合的重複計算陷阱。