MySQL GROUP BY 子句詳解：分組統計實戰指南

MySQL GROUP BY 子句詳解：分組統計實戰指南

GROUP BY 是 SQL 中用於數據分組聚合的核心子句，配合 COUNT、SUM、AVG 等聚合函數，能輕鬆實現各類統計分析需求。本文通過實例詳細講解其用法。

範例數據準備

假設有一張員工打字記錄表 employee_tbl：

mysql> SELECT * FROM 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                |
+------+------+------------+--------------------+

基礎用法：統計每位員工工作天數

直接使用 COUNT(*) 只能得到總行數：

mysql> SELECT COUNT(*) FROM employee_tbl;
+----------+
| COUNT(*) |
+----------+
| 7        |
+----------+

加上 GROUP BY name 即可按姓名分組統計：

mysql> SELECT name, COUNT(*) 
-> FROM employee_tbl 
-> GROUP BY name;
+------+----------+
| name | COUNT(*) |
+------+----------+
| Jack | 2        |
| Jill | 1        |
| John | 1        |
| Ram  | 1        |
| Zara | 2        |
+------+----------+

進階應用：多字段分組與多聚合函數

可同時統計多個指標，例如每人總打字頁數、平均值、最高值：

SELECT 
    name,
    COUNT(*) AS work_days,
    SUM(daily_typing_pages) AS total_pages,
    AVG(daily_typing_pages) AS avg_pages,
    MAX(daily_typing_pages) AS max_pages
FROM employee_tbl
GROUP BY name;

注意事項

• SELECT 列表中的非聚合欄位必須出現在 GROUP BY 中（MySQL 5.7+ 默認開啟 ONLY_FULL_GROUP_BY 模式）。
• 分組字段建議建立索引以提升查詢效能。
• WHERE 在分組前過濾，HAVING 在分組後過濾聚合結果。