MySQL 跨表分組統計實戰：多表關聯與條件聚合的單語句解法

場景與數據

四張表 A、B、C、D，需單條 SQL 實現：

• 輸出 A 表所有列（a, b）
• B 表按 a 關聯聚合 COUNT(*)
• 通過 C 表關聯 D 表獲取 D.a
• 無匹配時計數顯示 0

目標結果

a  b  count  D.a
1  1  3      1
2  2  1      2
3  4  0      3
4  6  0      4

解決方案：CTE + LEFT JOIN + 聚合子查詢

WITH 
A AS (
  SELECT 1 AS a, 1 AS b UNION ALL
  SELECT 2, 2 UNION ALL
  SELECT 3, 4 UNION ALL
  SELECT 4, 6
),
B_agg AS (
  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
  ) B
  GROUP BY a
),
C AS (
  SELECT 1 AS a, 'A' AS d UNION ALL
  SELECT 2, 'B' UNION ALL
  SELECT 3, 'C' UNION ALL
  SELECT 4, 'D'
),
D AS (
  SELECT 1 AS a, 'A' AS d UNION ALL
  SELECT 2, 'B' UNION ALL
  SELECT 3, 'C' UNION ALL
  SELECT 4, 'D'
)
SELECT 
  A.a, 
  A.b, 
  IFNULL(B_agg.cnt, 0) AS c, 
  D.a
FROM A
LEFT JOIN B_agg ON A.a = B_agg.a
JOIN C ON A.a = C.a
JOIN D ON C.d = D.d;

關鍵點解析

1. WITH 子句（CTE）：將臨時數據結構化，提升可讀性（MySQL 8.0+ 支持，舊版可用派生表）。
2. LEFT JOIN B_agg：保留 A 表所有行，無匹配時 IFNULL 補 0。
3. C、D 內連接：作為橋接表，C.d = D.d 確保關聯正確。
4. 聚合下推：B_agg 先聚合再關聯，避免重複計算。

MySQL 5.7 兼容寫法（派生表）

SELECT 
  A.a, A.b, IFNULL(B_agg.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
) A
LEFT JOIN (
  SELECT a, COUNT(*) AS cnt FROM (
    SELECT 1 AS a UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 2
  ) B GROUP BY a
) B_agg ON A.a = B_agg.a
JOIN (
  SELECT 1 AS a, 'A' AS d UNION ALL SELECT 2,'B' UNION ALL SELECT 3,'C' UNION ALL SELECT 4,'D'
) 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'
) D ON C.d = D.d;

優化建議

• 實際業務中 A/B/C/D 應為真實表，配合索引（A.a、B.a、C.a、C.d、D.d）。
• 大數據量時考慮物化視圖或中間表預聚合。