MySQL 分組查詢完全指南：GROUP BY、HAVING、排序執行順序與避坑

標準語法結構

SELECT column, aggregate_function
FROM table
[WHERE condition]
[GROUP BY expression]
[HAVING group_condition]
[ORDER BY column];

執行順序（關鍵）

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

WHERE 不能使用聚合函數/別名，HAVING 專門過濾分組後結果。

實戰示例

1. 基礎分組統計

SELECT deptno, COUNT(*) AS cnt, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno;

2. HAVING 過濾分組

SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000;  -- 僅顯示平均薪資>2000的部門

3. 多列分組

SELECT deptno, job, COUNT(*)
FROM emp
GROUP BY deptno, job;

4. GROUP BY 與 ORDER BY 協作

SELECT deptno, COUNT(*) AS cnt
FROM emp
GROUP BY deptno
ORDER BY cnt DESC;  -- 按統計結果排序

MySQL 特有擴展（非標準 SQL）

• GROUP BY col1, col2 時，SELECT 列表可包含未分組、未聚合的列，MySQL 任意取一行值（ONLY_FULL_GROUP_BY 關閉時）；
• 生產環境強烈建議開啟 sql_mode=ONLY_FULL_GROUP_BY，強制符合標準，避免數據不確定性。

效能優化技巧