MySQL GROUP BY 與 HAVING 實戰教學：從分組統計到條件篩選

核心概念釐清

GROUP BY：將結果集依指定欄位分組，配合聚合函數（COUNT、SUM、AVG 等）進行統計。

HAVING：對分組後的聚合結果進行篩選，類似 WHERE 但作用於分組層級。

關鍵規則：SELECT 清單中的非聚合欄位，必須出現在 GROUP BY 子句中。

範例資料準備

員工工作記錄表 employee_tbl：

+------+------+------------+--------------------+
| id   | name | work_date  | daily_typing_pages |
+------+------+------------+--------------------+
| 1    | John | 2007-01-24 | 250                |
| 2    | Ram  | 2007-05-27 | 220                |
| 3    | Jack | 2007-05-06 | 170                |
| 3    | Jack | 2007-04-06 | 100                |
| 4    | Jill | 2007-04-06 | 220                |
| 5    | Zara | 2007-06-06 | 300                |
| 5    | Zara | 2007-02-06 | 350                |
+------+------+------------+--------------------+

需求一：統計每位員工工作天數

SELECT name, COUNT(*) AS work_days
FROM employee_tbl
GROUP BY name;

結果：

+------+-----------+
| name | work_days |
+------+-----------+
| Jack | 2         |
| Jill | 1         |
| John | 1         |
| Ram  | 1         |
| Zara | 2         |
+------+-----------+

需求二：篩選工作天數超過 1 天的員工

SELECT name, COUNT(*) AS work_days
FROM employee_tbl
GROUP BY name
HAVING COUNT(*) > 1;

結果：

+------+-----------+
| name | work_days |
+------+-----------+
| Jack | 2         |
| Zara | 2         |
+------+-----------+

WHERE vs HAVING 關鍵差異

進階組合範例

多欄位分組 + 複雜 HAVING 條件

-- 統計各部門各職級平均薪資，僅顯示平均薪資 > 50000 且人數 >= 3 的群組
SELECT department, level, AVG(salary) AS avg_salary, COUNT(*) AS emp_count
FROM employees
WHERE hire_date < '2023-01-01'  -- 先過濾入職早於某日期的員工
GROUP BY department, level
HAVING AVG(salary) > 50000 AND COUNT(*) >= 3
ORDER BY avg_salary DESC;

搭配窗口函數（MySQL 8.0+）

-- 找出各部門薪資前 3 高的員工
WITH ranked AS (
    SELECT 
        name, department, salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 3;

效能優化提示

• GROUP BY 欄位建立索引可大幅加速分組
• WHERE 先過濾減少參與分組的資料量
• 避免 SELECT *，僅查詢必要欄位
• 大數據集考慮使用近似聚合（如 APPROX_COUNT_DISTINCT）