02 聚合函数与分组查询
📖 本节导读
- 认识五大聚合函数 COUNT / SUM / AVG / MAX / MIN：把一堆行"压缩"成一个统计值。
- 弄清 COUNT(*) 与 COUNT(列) 的区别——关键就在 NULL 上，用 score 表的真实 NULL 数据演示。
- 学会 GROUP BY 分组统计：按课程算平均分、按班级数人数，并理解 only_full_group_by 规则。
- 分清 HAVING 与 WHERE，最后记住一条 SQL 的真实执行顺序。
一、什么是聚合函数
之前学的函数（如 UPPER、ROUND）都是"一行进、一行出"。而聚合函数是"多行进、一个值出"——就像把一整袋苹果放上秤，最后只报一个总重量。
五大常用聚合函数：
| 函数 | 作用 | 类比 |
|---|---|---|
| COUNT(...) | 数个数 | 点名数人头 |
| SUM(列) | 求和 | 把金额加总 |
| AVG(列) | 求平均 | 算平均分 |
| MAX(列) | 求最大 | 找最高分 |
| MIN(列) | 求最小 | 找最低分 |
先来一个"全校成绩总览"：
SELECT SUM(score) AS 总分,
AVG(score) AS 平均分,
MAX(score) AS 最高分,
MIN(score) AS 最低分
FROM score;
+-------+----------+-----------+-----------+
| 总分 | 平均分 | 最高分 | 最低分 |
+-------+----------+-----------+-----------+
| 868.0 | 78.90909 | 95.0 | 58.0 |
+-------+----------+-----------+-----------+
1 row in set (0.00 sec)
💡 平均分小数位很多？外面套一层 ROUND 即可：
ROUND(AVG(score), 1)得到 78.9。
二、COUNT(*) 与 COUNT(列)：NULL 是分水岭
score 表一共 12 行，其中陈刚的计算机成绩（id=12）是 NULL。现在对比三种写法：
SELECT COUNT(*) AS 行数,
COUNT(score) AS 有成绩的行数,
COUNT(DISTINCT student_id) AS 有成绩记录的学生数
FROM score;
+--------+--------------------+--------------------------+
| 行数 | 有成绩的行数 | 有成绩记录的学生数 |
+--------+--------------------+--------------------------+
| 12 | 11 | 5 |
+--------+--------------------+--------------------------+
1 row in set (0.00 sec)
- COUNT(*)：数"行"，不管这一行里有没有 NULL，一共 12 行。
- COUNT(score)：只数 score 列不是 NULL 的行，NULL 那行被跳过，所以是 11。
- COUNT(DISTINCT 列)：去重后再数，5 个学生。
其实 SUM、AVG、MAX、MIN 也都自动忽略 NULL。这在算平均分时影响很大——以计算机课（course_id = 3，成绩为 88、70、81、NULL）为例：
SELECT AVG(score) AS 忽略NULL的平均分,
AVG(IFNULL(score, 0)) AS 把NULL当0的平均分
FROM score
WHERE course_id = 3;
+------------------------+-------------------------+
| 忽略NULL的平均分 | 把NULL当0的平均分 |
+------------------------+-------------------------+
| 79.66667 | 59.75000 |
+------------------------+-------------------------+
1 row in set (0.00 sec)
- 忽略 NULL：(88 + 70 + 81) ÷ 3 = 79.66667。
- 当 0 算：(88 + 70 + 81 + 0) ÷ 4 = 59.75。
两种口径都"对"，关键是你想要哪种业务含义：缺考不参与平均，还是缺考按 0 分算？
三、GROUP BY —— 分组统计
上面统计的都是"全表一个数"。想知道每门课的平均分呢？这就要先"分堆"再统计——GROUP BY 就是"分堆"的动作：把 course_id 相同的行分到同一堆，聚合函数在每一堆内部各算一次。
按 course_id 统计各科平均分
SELECT course_id,
COUNT(*) AS 记录数,
COUNT(score) AS 有效成绩数,
AVG(score) AS 平均分
FROM score
GROUP BY course_id;
+-----------+-----------+-----------------+----------+
| course_id | 记录数 | 有效成绩数 | 平均分 |
+-----------+-----------+-----------------+----------+
| 1 | 4 | 4 | 79.75000 |
| 2 | 4 | 4 | 77.50000 |
| 3 | 4 | 3 | 79.66667 |
+-----------+-----------+-----------------+----------+
3 rows in set (0.00 sec)
验算一下：
- 数学（1）：(90 + 76 + 95 + 58) ÷ 4 = 79.75
- 英语（2）：(85 + 92 + 60 + 73) ÷ 4 = 77.5
- 计算机（3）：4 条记录，但 AVG 忽略 NULL，(88 + 70 + 81) ÷ 3 = 79.66667
按 class_id 统计各班人数
SELECT class_id, COUNT(*) AS 人数
FROM student
GROUP BY class_id;
+----------+--------+
| class_id | 人数 |
+----------+--------+
| 1 | 3 |
| 2 | 2 |
+----------+--------+
2 rows in set (0.00 sec)
一班有张三、李四、陈刚 3 人，二班有王小红、赵敏 2 人。
WHERE 也能和 GROUP BY 搭配
WHERE 在分组之前先过滤行。比如"只统计及格（>= 60）成绩的各科平均分"：
SELECT course_id, AVG(score) AS 及格者平均分
FROM score
WHERE score >= 60
GROUP BY course_id;
+-----------+-----------------+
| course_id | 及格者平均分 |
+-----------+-----------------+
| 1 | 87.00000 |
| 2 | 77.50000 |
| 3 | 79.66667 |
+-----------+-----------------+
3 rows in set (0.00 sec)
数学的 58 分被 WHERE 先淘汰，剩 (90 + 76 + 95) ÷ 3 = 87。
四、GROUP BY 与 SELECT 列的规则（only_full_group_by）
一个新手必踩的错误：分了组之后，SELECT 里随手多写一列。
SELECT class_id, name, COUNT(*) FROM student GROUP BY class_id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'school.student.name' which is not
functionally dependent on columns in GROUP BY clause; this is incompatible
with sql_mode=only_full_group_by
为什么报错？分完组后，一堆行被压成了一行。class_id = 1 这一堆里有张三、李四、陈刚三个名字，输出只有一行，name 该显示谁的？MySQL 无法替你决定，干脆报错（MySQL 5.7 之后默认开启 only_full_group_by 模式，就是为了拦住这种歧义写法）。
规则：SELECT 里只能出现两类东西——
- GROUP BY 后面出现过的列（每堆内该列值都一样，不会有歧义）；
- 聚合函数（本来就是把一堆算成一个值）。
想把"堆里的名字"也列出来，可以用 GROUP_CONCAT 把它们拼成一串：
SELECT class_id, COUNT(*) AS 人数, GROUP_CONCAT(name) AS 名单
FROM student
GROUP BY class_id;
+----------+--------+----------------------+
| class_id | 人数 | 名单 |
+----------+--------+----------------------+
| 1 | 3 | 张三,李四,陈刚 |
| 2 | 2 | 王小红,赵敏 |
+----------+--------+----------------------+
2 rows in set (0.00 sec)
五、HAVING 与 WHERE 的区别
需求：查出平均分大于 80 的课程。有人会这样写：
SELECT course_id, AVG(score) FROM score WHERE AVG(score) > 80 GROUP BY course_id;
ERROR 1111 (HY000): Invalid use of group function
报错了！因为 WHERE 在分组之前执行，那时每行只是一条孤零零的成绩，平均分根本还没算出来，自然不能用聚合函数做条件。
对分组结果做筛选，要用 HAVING，它在分组之后执行：
SELECT course_id, AVG(score) AS 平均分
FROM score
GROUP BY course_id
HAVING AVG(score) > 80;
Empty set (0.00 sec)
结果是空的——这不是写错了，而是三门课的平均分（79.75、77.5、79.66667）确实都没超过 80。空结果也是正确答案。把门槛降到 78 再看：
SELECT course_id, AVG(score) AS 平均分
FROM score
GROUP BY course_id
HAVING AVG(score) > 78;
+-----------+----------+
| course_id | 平均分 |
+-----------+----------+
| 1 | 79.75000 |
| 3 | 79.66667 |
+-----------+----------+
2 rows in set (0.00 sec)
再来一个 HAVING 用 COUNT 的例子——找出人数超过 2 人的班级：
SELECT class_id, COUNT(*) AS 人数
FROM student
GROUP BY class_id
HAVING COUNT(*) > 2;
+----------+--------+
| class_id | 人数 |
+----------+--------+
| 1 | 3 |
+----------+--------+
1 row in set (0.00 sec)
一句话总结两者分工：
| 对比项 | WHERE | HAVING |
|---|---|---|
| 过滤对象 | 一行一行的原始数据 | 一堆一堆的分组结果 |
| 执行时机 | 分组之前 | 分组之后 |
| 能否用聚合函数 | 不能 | 能 |
类比：WHERE 是入场安检（不合格的人根本进不了考场），HAVING 是赛后按团体总分淘汰（先比完赛、算完成绩才筛）。
六、SQL 的执行顺序
我们书写的顺序是 SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT，但 MySQL 执行的顺序是：
FROM → 先确定查哪张表
WHERE → 逐行过滤原始数据
GROUP BY → 把剩下的行分堆
HAVING → 过滤分组结果
SELECT → 计算要输出的列（聚合、别名在这里生效）
ORDER BY → 对最终结果排序
LIMIT → 截取前几行
理解了这个顺序，前面所有"为什么"就都通了：
- WHERE 里不能用聚合函数——执行到 WHERE 时还没分组；
- WHERE 里不能用 SELECT 起的别名——别名在 SELECT 阶段才诞生；
- ORDER BY 里可以用别名——它在 SELECT 之后执行。
综合示例：各科平均分从高到低，只要第一名：
SELECT course_id, AVG(score) AS 平均分
FROM score
GROUP BY course_id
ORDER BY 平均分 DESC
LIMIT 1;
+-----------+----------+
| course_id | 平均分 |
+-----------+----------+
| 1 | 79.75000 |
+-----------+----------+
1 row in set (0.00 sec)
⚠️ 新手常见坑
- COUNT(列) 会跳过 NULL：数总行数用 COUNT(*)，数"某列有值的行数"才用 COUNT(列)，两者不通用。
- AVG 忽略 NULL 而不是当 0 算：缺考要不要按 0 分参与平均，需要自己用
AVG(IFNULL(score, 0))明确表达。 - SELECT 里写了非分组列：触发 ERROR 1055（only_full_group_by）。记住口诀"要么在 GROUP BY 里，要么在聚合函数里"。
- 把聚合条件写进 WHERE：
WHERE AVG(score) > 80会报 ERROR 1111，对分组结果筛选必须用 HAVING。 - HAVING 查出空结果就慌：Empty set 不代表 SQL 写错，可能只是真的没有满足条件的组。
📝 小结
- 聚合函数把多行压缩成一个值：COUNT 数数、SUM 求和、AVG 平均、MAX/MIN 最值；除 COUNT(*) 外都忽略 NULL。
- COUNT(*) 数行数（12），COUNT(score) 数非 NULL 值（11），COUNT(DISTINCT 列) 去重后数。
- GROUP BY 先分堆再统计；SELECT 里只能出现分组列和聚合函数，否则触发 only_full_group_by 报错。
- WHERE 过滤"行"（分组前），HAVING 过滤"组"（分组后），聚合条件只能放 HAVING。
- 执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。
✍️ 练习题
- 统计英语课（course_id = 2）有多少人有成绩、平均分是多少。
参考答案
SELECT COUNT(score) AS 人数, AVG(score) AS 平均分
FROM score
WHERE course_id = 2;
人数 4，平均分 77.50000（(85+92+60+73)÷4）。
- 按 student_id 统计每个学生的最高分和最低分。
参考答案
SELECT student_id, MAX(score) AS 最高分, MIN(score) AS 最低分
FROM score
GROUP BY student_id;
1: 90.0/85.0；2: 92.0/76.0；3: 95.0/60.0；4: 81.0/58.0；5: 73.0/73.0（NULL 被忽略）。
- 查询"选课记录数达到 3 条"的学生（按 student_id 分组，用 COUNT(*)）。
参考答案
SELECT student_id, COUNT(*) AS 选课数
FROM score
GROUP BY student_id
HAVING COUNT(*) >= 3;
student_id 1（张三，3 条）、3（王小红，3 条）。注意陈刚虽然有 NULL 成绩，但 COUNT(*) 数的是行，他也有 2 条记录，只是没到 3。
- 下面这条 SQL 为什么报错？该怎么改？
SELECT student_id, course_id, AVG(score) FROM score GROUP BY student_id;
参考答案
course_id 既不在 GROUP BY 里，也没套聚合函数，违反 only_full_group_by（ERROR 1055）：每个学生对应多门课，压成一行后 course_id 无法确定显示哪一个。改法：把 course_id 从 SELECT 里删掉，或改成 GROUP_CONCAT(course_id)，或把它加进 GROUP BY（但那样分组含义就变了）。
- 按 course_id 分组，查出"最低分低于 60"的课程及其最低分。
参考答案
SELECT course_id, MIN(score) AS 最低分
FROM score
GROUP BY course_id
HAVING MIN(score) < 60;
course_id = 1（数学），最低分 58.0（赵敏）。