SQL 兩表分類匯總合並查詢：UNION ALL 分組聚合

問題描述

表1（采購計劃）、表2（實際采購）結構相同，需按類型+名稱分組，輸出計劃數與實際數對比。

表結構

表1: type, name, plan_qty
表2: type, name, actual_qty

期望輸出

type  | name  | plan_qty | actual_qty
文具   | 鉛筆   | 2        | 1
耗材   | 紙     | 10       | 1

標準解法：UNION ALL + 分組聚合

SELECT 
    type, 
    name,
    SUM(plan_qty) AS plan_qty,
    SUM(actual_qty) AS actual_qty
FROM (
    SELECT type, name, qty AS plan_qty, 0 AS actual_qty FROM table1
    UNION ALL
    SELECT type, name, 0 AS plan_qty, qty AS actual_qty FROM table2
) t
GROUP BY type, name
ORDER BY type, name;

關鍵點解析

1. UNION ALL 而非 UNION：保留重複行，性能更好（不去重）
2. 列對齊：兩查詢列數、順序、類型一致，用 0 佔位
3. 子查詢別名：`FROM (...) t` 必須起別名
4. 聚合函數：`SUM` 累加計劃量和實際量

替代方案：FULL OUTER JOIN

SELECT 
    COALESCE(t1.type, t2.type) AS type,
    COALESCE(t1.name, t2.name) AS name,
    t1.plan_qty,
    t2.actual_qty
FROM (
    SELECT type, name, SUM(qty) AS plan_qty FROM table1 GROUP BY type, name
) t1
FULL OUTER JOIN (
    SELECT type, name, SUM(qty) AS actual_qty FROM table2 GROUP BY type, name
) t2 ON t1.type = t2.type AND t1.name = t2.name;

優點：單次掃描聚合後連接。缺點：MySQL 不支持 `FULL OUTER JOIN`，需 `UNION` 模擬。

MySQL 兼容寫法

SELECT type, name, SUM(plan_qty), SUM(actual_qty) FROM (
    SELECT type, name, qty AS plan_qty, 0 AS actual_qty FROM table1
    UNION ALL
    SELECT type, name, 0, qty FROM table2
) t GROUP BY type, name;

性能優化建議

• 在 `type, name` 上建立聯合索引
• 大數據量考慮分區表或物化視圖
• 避免 `SELECT *`，只查所需列

常見錯誤

總結

兩表同結構分類匯總，UNION ALL 合併後 GROUP BY 是最通用、兼容性最好、性能穩健的方案。MySQL 用此法；PostgreSQL/Oracle/SQL Server 可用 `FULL OUTER JOIN` 簡化。