MySQL GROUP BY 與 HAVING 組合使用避坑指南

MySQL GROUP BY 非標準行為與 HAVING 正確用法

MySQL 寬鬆 GROUP BY 行為

標準 SQL 要求：SELECT 列表中非聚合欄位**必須**出現在 GROUP BY 中。但 MySQL 預設允許：

SELECT id, COUNT(1), aa FROM t GROUP BY id;
-- aa 未在 GROUP BY，MySQL 返回該組第一行的 aa 值（非確定性）

風險：結果不可預測，不同版本/配置可能不同。生產**嚴禁**依賴此行為。

強制標準模式

SET sql_mode = 'ONLY_FULL_GROUP_BY';
-- 或 my.cnf 永久生效：
[mysqld]
sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

正確寫法

-- 方案 1：aa 加入 GROUP BY
SELECT id, COUNT(1), aa FROM t GROUP BY id, aa;

-- 方案 2：aa 用聚合函數
SELECT id, COUNT(1), MAX(aa) FROM t GROUP BY id;

-- 方案 3：子查詢先聚合再關聯詳情
SELECT t1.id, t1.cnt, t2.aa
FROM (SELECT id, COUNT(1) AS cnt FROM t GROUP BY id) t1
JOIN t t2 ON t1.id = t2.id
LIMIT 1;  -- 取任意一行

HAVING 與 WHERE 差異

實例：學生成績

-- 原始表：student, subject, score
-- 每生總分 > 180
SELECT student, SUM(score) AS total
FROM scores
GROUP BY student
HAVING SUM(score) > 180;

-- 等價於（但效能較差）
SELECT student, total FROM (
    SELECT student, SUM(score) AS total FROM scores GROUP BY student
) t WHERE total > 180;

HAVING 常見誤區

• HAVING score = 80：若 score 未聚合，MySQL 允許但語義模糊（取組內第一行），應改 HAVING MAX(score) = 80 或 HAVING MIN(score) = 80
• WHERE 能解決的**不要**用 HAVING（WHERE 可用索引，HAVING 不可）

最佳實踐

1. 永久開啟 ONLY_FULL_GROUP_BY
2. SELECT 非聚合欄位必寫入 GROUP BY 或包裝聚合函數
3. HAVING 僅用於聚合條件過濾
4. 複雜邏輯用 CTE/子查詢分步清晰化