GROUP BY 核心概念

GROUP BY 將結果集依指定欄位分組，配合聚合函數（COUNT、SUM、AVG、MAX、MIN 等）對每組進行彙總計算。

基本語法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name
HAVING aggregate_condition
ORDER BY column_name;

建立範例資料

CREATE TABLE employee (
    id INT, first_name VARCHAR(15), last_name VARCHAR(15),
    start_date DATE, end_date DATE, salary FLOAT(8,2),
    city VARCHAR(10), description VARCHAR(15)
);
INSERT INTO employee VALUES
(1,'Jason','Martin','1996-07-25','2006-07-25',1234.56,'Toronto','Programmer'),
(2,'Alison','Mathews','1976-03-21','1986-02-21',6661.78,'Vancouver','Tester'),
(3,'James','Smith','1978-12-12','1990-03-15',6544.78,'Vancouver','Tester'),
(4,'Celia','Rice','1982-10-24','1999-04-21',2344.78,'Vancouver','Manager'),
(5,'Robert','Black','1984-01-15','1998-08-08',2334.78,'Vancouver','Tester'),
(6,'Linda','Green','1987-07-30','1996-01-04',4322.78,'New York','Tester'),
(7,'David','Larry','1990-12-31','1998-02-12',7897.78,'New York','Manager'),
(8,'James','Cat','1996-09-17','2002-04-15',1232.78,NULL,'Tester');  -- city 為 NULL

經典查詢範例

1. 基礎分組計數

SELECT city, COUNT(*) AS cnt
FROM employee
GROUP BY city;

結果：

+-----------+-----+
| city      | cnt |
+-----------+-----+
| NULL      | 1   |
| New York  | 2   |
| Toronto   | 1   |
| Vancouver | 4   |
+-----------+-----+

重點：NULL 會被視為一個獨立群組。

2. 加上 WHERE 過濾（分組前過濾）

SELECT city, COUNT(*)
FROM employee
WHERE description IN ('Tester','Programmer')
GROUP BY city;

結果：Manager 被排除，Vancouver 變 4→3，New York 變 2→1。

3. 加上 HAVING 過濾（分組後過濾）

SELECT city, COUNT(*) AS cnt
FROM employee
WHERE description IS NOT NULL
GROUP BY city
HAVING cnt > 2;

結果：僅 Vancouver (4) 符合。

4. 多欄位分組

SELECT city, description, COUNT(*), AVG(salary)
FROM employee
GROUP BY city, description;

5. 僅用 GROUP BY 不加聚合（MySQL 延伸語法）

SELECT * FROM employee GROUP BY city;

⚠️ 非標準 SQL，MySQL 預設 ONLY_FULL_GROUP_BY 會報錯。回傳的非分組欄位值是「任意一列」，不可依賴。生產環境請開啟 sql_mode=ONLY_FULL_GROUP_BY。

WHERE vs HAVING 關鍵差異

特性	WHERE	HAVING
執行時機	分組前	分組後
可用聚合函數	❌	✅
可用別名	❌	✅ (MySQL 延伸)
索引利用	✅ 可用索引篩選	❌ 需掃描分組結果

原則：能用 WHERE 就別用 HAVING，越早過濾資料量越小。

NULL 值處理技巧

• GROUP BY city 會將 NULL 歸為一組
• 若要忽略 NULL 群組：WHERE city IS NOT NULL
• 若要將 NULL 顯示為自訂標籤：SELECT COALESCE(city, 'Unknown') AS city, ... GROUP BY city

效能優化建議

1. 索引支援：在 GROUP BY 欄位建立索引（INDEX idx_city (city)），可避免 Using temporary; Using filesort
2. 減少分組欄位：只分組必要欄位，避免高基數欄位（如 id）導致群組數爆炸
3. 善用覆蓋索引：INDEX idx_city_desc (city, description) 可直接從索引取得資料，避免回表
4. 大表分頁：配合 LIMIT 時，先在子查詢分組再 JOIN 回主表取明細

MySQL 8.0 新功能：窗格函數替代部分 GROUP BY

-- 每個城市的員工數，不壓縮列
SELECT *, COUNT(*) OVER (PARTITION BY city) AS city_cnt
FROM employee;

窗格函數保留明細列，適合需同時顯示明細與彙總的報表。

總結

GROUP BY 是 SQL 資料分析的核心工具。掌握 WHERE 前置過濾、HAVING 後置過濾、NULL 群組處理、索引優化，並理解 ONLY_FULL_GROUP_BY 語意，即可寫出正確且高效的分組查詢。