MySQL 分組統計實戰：多表關聯條件計數與 LEFT JOIN 消除 NULL 技巧

場景：四張表 A(a,b)、B(a,c)、C(a,d)、D(d,e)。需求：輸出 A 所有行，關聯 B 統計 a 出現次數，經 C 關聯 D 取 D.a。結果集含 A.a, A.b, B_count, D.a，無匹配時計數顯示 0。

標準 SQL 解法：子查詢聚合 + 多層 JOIN

SELECT 
    A.a,
    A.b,
    IFNULL(B.cnt, 0) AS b_count,
    D.a AS 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, 1 AS c UNION ALL
        SELECT 1, 3 UNION ALL
        SELECT 1, 4 UNION ALL
        SELECT 2, 1
    ) 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 'A' AS d, 1 AS a UNION ALL
    SELECT 'B', 2 UNION ALL
    SELECT 'C', 3 UNION ALL
    SELECT 'D', 4
) AS D ON C.d = D.d;

關鍵技術點解析

1. 派生表：(SELECT ... UNION ALL ...) AS alias 在查詢中構建臨時結果集，避免建實體表。
2. 先聚合後關聯：B 表先 GROUP BY a 計數，再 LEFT JOIN 到 A，**避免一對多膨脹導致重複計數**。
3. LEFT JOIN 保留主表所有行：A 為駕駛表，B 無匹配時 B.cnt 為 NULL。
4. IFNULL/COALESCE 消除 NULL：IFNULL(B.cnt, 0) 將無匹配計數轉為 0，符合業務語義。
5. 鏈式關聯：A → C → D，透過 C 的 d 欄位橋接 D，最終取 D.a。

效能優化建議（大表場景）

• 派生表改為實體表或視圖，並建立適當索引：
  INDEX idx_B_a (a)、INDEX idx_C_a (a)、INDEX idx_D_d (d)。
• 大表聚合先篩選：WHERE 子句下推到派生表內部（MySQL 5.6+ derived_merge 優化）。
• 避免 SELECT *，僅選必要欄位減少網路與臨時表開銷。
• 若 D 表極小（字典表），可考慮 JOIN 時用 STRAIGHT_JOIN 固定驅動順序。

結果驗證

完全符合預期：A 表 4 行全保留，B 計數正確，無匹配顯示 0，D.a 正確關聯。