GROUP BY 將結果集按指定欄位分組,配合聚合函數對每組進行統計計算。若無 GROUP BY,聚合函數作用於整個結果集(視為單一群組)。
| 函數 | 說明 | 忽略 NULL | 範例 |
|---|---|---|---|
COUNT(expr) | 非 NULL 值筆數 | 是 | COUNT(*) 計算行數(含 NULL) |
SUM(expr) | 數值總和 | 是 | SUM(salary) |
AVG(expr) | 平均值 | 是 | AVG(score) |
MIN(expr) | 最小值 | 是 | MIN(price) |
MAX(expr) | 最大值 | 是 | MAX(created_at) |
GROUP_CONCAT(expr) | 字串串聯 | 是 | GROUP_CONCAT(name SEPARATOR ',') |
STDDEV(expr) | 標準差 | 是 | STDDEV(score) |
VARIANCE(expr) | 變異數 | 是 | VARIANCE(score) |
BIT_AND/OR/XOR | 位元運算 | 是 | BIT_AND(flags) |
-- 建表
CREATE TABLE scores (
id INT AUTO_INCREMENT PRIMARY KEY,
student_name VARCHAR(50),
subject VARCHAR(20),
score INT
);
-- 插入測試資料
INSERT INTO scores (student_name, subject, score) VALUES
('小明', '數學', 85), ('小明', '英文', 92), ('小明', '國文', 78),
('小華', '數學', 76), ('小華', '英文', 88), ('小華', '國文', 90),
('小美', '數學', 95), ('小美', '英文', 89);
SELECT student_name,
COUNT(*) AS 科目數,
SUM(score) AS 總分,
AVG(score) AS 平均,
MIN(score) AS 最低,
MAX(score) AS 最高
FROM scores
GROUP BY student_name;
結果:
student_name | 科目數 | 總分 | 平均 | 最低 | 最高 小明 | 3 | 255 | 85.00 | 78 | 92 小華 | 3 | 254 | 84.67 | 76 | 90 小美 | 2 | 184 | 92.00 | 89 | 95
SELECT student_name,
GROUP_CONCAT(subject ORDER BY score DESC SEPARATOR ' > ') AS 強項排序,
GROUP_CONCAT(score ORDER BY score DESC SEPARATOR ', ') AS 分數分布
FROM scores
GROUP BY student_name;
結果:
小明 | 英文 > 數學 > 國文 | 92, 85, 78 小華 | 國文 > 英文 > 數學 | 90, 88, 76 小美 | 數學 > 英文 | 95, 89
SELECT department, job_title,
COUNT(*) AS 人數,
AVG(salary) AS 平均薪資
FROM employees
GROUP BY department, job_title
ORDER BY department, 平均薪資 DESC;
-- 只有平均分 > 85 的學生
SELECT student_name, AVG(score) AS 平均
FROM scores
GROUP BY student_name
HAVING AVG(score) > 85;
⚠️ WHERE 過濾行,HAVING 過濾群組,執行順序:WHERE → GROUP BY → HAVING
SELECT student_name, subject, AVG(score)
FROM scores
GROUP BY student_name, subject WITH ROLLUP;
輸出包含:各學生各科平均 → 各學生總平均(subject=NULL) → 全班總平均(student_name=NULL, subject=NULL)
SELECT student_name,
SUM(CASE WHEN subject = '數學' THEN score END) AS 數學,
SUM(CASE WHEN subject = '英文' THEN score END) AS 英文,
SUM(CASE WHEN subject = '國文' THEN score END) AS 國文
FROM scores
GROUP BY student_name;
-- MySQL 嚴格模式下報錯(ONLY_FULL_GROUP_BY)
SELECT student_name, subject, AVG(score) -- subject 未分組也非聚合
FROM scores
GROUP BY student_name;
✅ 修正:加入分組、用聚合函數、或使用 ANY_VALUE()
-- 錯誤
SELECT student_name, AVG(score)
FROM scores
WHERE AVG(score) > 80
GROUP BY student_name;
-- 正確:用 HAVING
SELECT student_name, AVG(score)
FROM scores
GROUP BY student_name
HAVING AVG(score) > 80;
-- COUNT(*) 計算所有行
-- COUNT(column) 忽略 NULL
-- SUM/AVG 等忽略 NULL,全 NULL 回傳 NULL
-- 若需將 NULL 視為 0 計算
SELECT AVG(COALESCE(score, 0)) FROM scores;
INDEX idx_student (student_name)tmp_table_size, max_heap_table_size 避免磁碟臨時表<?php
$conn = new mysqli("localhost", "user", "pass", "school");
$sql = "SELECT student_name,
COUNT(*) AS subjects,
SUM(score) AS total,
ROUND(AVG(score), 2) AS average,
GROUP_CONCAT(CONCAT(subject, ':', score) ORDER BY score DESC SEPARATOR '; ') AS details
FROM scores
GROUP BY student_name
HAVING COUNT(*) >= 2
ORDER BY average DESC";
$result = $conn->query($sql);
echo "學生 科目數 總分 平均 明細 ";
while ($row = $result->fetch_assoc()) {
echo "
{$row['student_name']}
{$row['subjects']}
{$row['total']}
{$row['average']}
{$row['details']}
";
}
echo "
";
$conn->close();
?>
GROUP BY 是資料分析的瑞士刀,善用聚合函數、條件聚合與 ROLLUP,讓複雜統計變得簡單優雅!