MySQL 分組統計實戰：多表關聯、LEFT JOIN 與 IFNULL 處理無對應資料

資料模型

A(a,b)  B(a,c)  C(a,d)  D(d,e)
1,1     1,1     1,A     A,1
2,2     1,3     2,B     B,2
3,4     1,4     3,C     C,3
4,6     2,1     4,D     D,4

需求：輸出 A 所有欄位、B 針對 A.a 分組計數、經 C 關聯 D 取 D.a。

期望結果：
a  b  c  D.a
1  1  3  1
2  2  1  2
3  4  0  3
4  6  0  4

SQL 解法

SELECT A.a, A.b, IFNULL(B.cnt, 0) AS c, D.a
FROM (
    SELECT 1 AS a, 1 AS b UNION ALL
    SELECT 2, 2 UNION ALL
    SELECT 3, 4 UNION ALL
    SELECT 4, 6
) AS A
LEFT JOIN (
    SELECT a, COUNT(*) AS cnt
    FROM (
        SELECT 1 AS a, 3 AS c UNION ALL
        SELECT 1, 3 UNION ALL
        SELECT 1, 3 UNION ALL
        SELECT 2, 3
    ) AS B GROUP BY a
) AS B ON A.a = B.a
JOIN (
    SELECT 1 AS a, 'A' AS d UNION ALL
    SELECT 2, 'B' UNION ALL
    SELECT 3, 'C' UNION ALL
    SELECT 4, 'D'
) AS C ON A.a = C.a
JOIN (
    SELECT 1 AS a, 'A' AS d UNION ALL
    SELECT 2, 'B' UNION ALL
    SELECT 3, 'C' UNION ALL
    SELECT 4, 'D'
) AS D ON D.d = C.d;

關鍵技巧

• 衍生表：(SELECT ... UNION ALL ...) AS alias 模擬實體表，方便測試。
• LEFT JOIN + IFNULL：保留左表所有列，右表無對應時補 0。
• 雙重 JOIN 關聯 D：透過 C 的 d 欄位橋接 D 表。

效能優化建議（實體表場景）

1. 在 B.a、C.a、D.d 建立索引。
2. 大表分組統計考慮 Summary Table 定時刷新，避免即時 COUNT(*) 全掃。
3. MySQL 8.0 可用 WITH (CTE) 重寫，可讀性更好。