聚合与 GROUP BY

这一课你将能做到

1. 用 COUNT / SUM / AVG / MAX / MIN 做全表统计，并用 GROUP BY 做分组统计
2. 用 HAVING 过滤分组结果，说清 WHERE 和 HAVING 的执行时点差异
3. 把 JOIN + WHERE + GROUP BY + HAVING + ORDER BY 串成一条完整的统计查询

前置：第一课建库脚本已跑（若做过第 6 课的写操作，先重跑脚本复原数据，保证输出一致）。

为什么要学

前面几课查的都是明细行；但业务方要的经常是统计口径：每个分类几门课、每个学生完成几节、各课程卖了多少钱。报表、看板、运营后台的数字，全是聚合查询产出的。聚合也是 SQL 面试的重灾区——COUNT 的三种写法、ONLY_FULL_GROUP_BY、WHERE 与 HAVING 的区别，几乎每轮都会问。

先建立图像

聚合就是「先分堆、再算数」：

17 条学习记录
   │  按 student_id 分堆（GROUP BY）
   ▼
┌────────┬────────┬────────┬─…─┐
│ s=1(4) │ s=2(2) │ s=3(4) │ … │   ← 每堆是一个「组」
└────────┴────────┴────────┴─…─┘
   │  每堆内 COUNT(*)、AVG(score)
   ▼
  每个学生一行：完成数、平均分

完整逻辑执行顺序（背下来，所有聚合疑问都能用它解释）：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
 取表   筛行     分堆      筛组     出结果   排序      截取

跟着做

步骤 1：五种聚合函数，全表一桶

SELECT MAX(price)  AS 最贵,
       MIN(price)  AS 最便宜,
       ROUND(AVG(price), 2) AS 均价,
       SUM(price)  AS 总价,
       COUNT(*)    AS 课程数
FROM course;
-- 399 / 0 / 149.29 / 1045 / 7

没有 GROUP BY 时，整张表就是一组，结果永远只有一行。

步骤 2：COUNT 的三种写法（本课第一个考点）

SELECT COUNT(*), COUNT(score), COUNT(DISTINCT student_id)
FROM study_record;
-- 17 / 16 / 6

• COUNT(*)：数行数，17 行
• COUNT(score)：数 score 非 NULL 的行，大鹏那条 score 是 NULL，所以 16
• COUNT(DISTINCT student_id)：去重后 6 个学生学过东西（小吴没学）

三个数字不一样，全都对——关键是你要的是哪个口径。

步骤 3：GROUP BY 单列分组

SELECT category,
       COUNT(*)             AS course_cnt,
       ROUND(AVG(price), 2) AS avg_price
FROM course
GROUP BY category;
-- java      2  249.00
-- database  3  133.00
-- frontend  2   74.00

「每个分类几门课、平均卖多少钱」一句话出来了。SELECT 里非聚合列（category）必须出现在 GROUP BY 中，原因见要点 2。

步骤 4：GROUP BY 多列分组

SELECT category, level, COUNT(*) AS cnt
FROM course
GROUP BY category, level;
-- 6 行：先按 category 分堆，堆内再按 level 分堆

步骤 5：HAVING 过滤组

SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM course
GROUP BY category
HAVING AVG(price) > 100;
-- java 249 / database 133（frontend 74 被筛掉）

HAVING 跑在分组之后，所以它能用聚合函数；WHERE 跑在分组之前，不能。这就是两者的全部区别。

步骤 6：聚合 + JOIN 组合（真实报表的样子）

每个学生的完成课时数，只看完成满 3 节的：

SELECT s.id, s.name, COUNT(*) AS done_cnt
FROM study_record r
JOIN student s ON r.student_id = s.id
WHERE r.completed = 1
GROUP BY s.id, s.name
HAVING COUNT(*) >= 3
ORDER BY done_cnt DESC, s.name;
-- 老陈 4 / 阿珍 4 / 老周 4

每个课程的实际收入（只算未退款订单）：

SELECT c.title,
       SUM(o.amount) AS revenue,
       COUNT(*)      AS order_cnt
FROM pay_order o
JOIN course c ON o.course_id = c.id
WHERE o.status = 'PAID'
GROUP BY c.id, c.title
ORDER BY revenue DESC;
-- MySQL 性能优化实战 798(2 单) / Java 基础入门 199(1 单) / 前端页面基础 198(2 单)

注意 WHERE 把 REFUNDED 行在分组前就筛掉了——先筛行、再分堆、再算数，顺序不能反。

步骤 7：小试窗口函数（MySQL 8 新能力，延伸预告）

「每个分类最贵的课程」用 GROUP BY 很别扭，窗口函数一行搞定：

SELECT category, title, price
FROM (
    SELECT c.category, c.title, c.price,
           ROW_NUMBER() OVER (PARTITION BY c.category ORDER BY c.price DESC) AS rn
    FROM course c
) t
WHERE rn = 1;
-- java: Java 面向对象进阶 299 / database: MySQL 性能优化实战 399 / frontend: 前端页面基础 99

看不懂没关系，知道 MySQL 8 有这个工具即可；本 track 只要求 GROUP BY 熟练。

讲清楚要点

1. 聚合函数把一组行算成一个值：COUNT/SUM/AVG/MAX/MIN。没有 GROUP BY 时全表一组；有 GROUP BY 时每组一行结果。
2. ONLY_FULL_GROUP_BY（MySQL 8 默认开启）：SELECT 里出现的列，要么在 GROUP BY 里、要么被聚合包着。想同时拿 id 和 name？GROUP BY s.id, s.name 是最稳写法——按主键分组时，同组内 name 唯一，语义明确。
3. WHERE 筛行（分组前），HAVING 筛组（分组后）：写聚合条件给 HAVING，写普通条件给 WHERE。能用 WHERE 先筛的绝不放 HAVING——少一分组数据，性能更好。
4. NULL 与聚合：COUNT(*) 数行数；COUNT(col)、SUM、AVG、MAX、MIN 都忽略 NULL。AVG(score) 是「非 NULL 分数的平均」，想给未完成记 0 分要显式 AVG(COALESCE(score, 0))。
5. GROUP BY 大列有代价：按 TEXT 之类的宽列分组会撑大临时表。业务上分组键用 id 或短列。
6. 组内 TopN、排名、环比这类需求，GROUP BY 表达起来很拧巴，MySQL 8 的窗口函数（ROW_NUMBER/RANK/OVER）才是正解，进阶 track 再展开。

你可能会懵的地方

• 为什么 SELECT 里不能随便写列？报 1055 错。 分堆之后，一个组里 id 各不相同，数据库不知道该显示哪个。ONLY_FULL_GROUP_BY 就是逼你回答这个问题。MySQL 5.7 之前默认不开，产出了大量「碰巧能跑」的错数据查询，8.0 默认开是对的。

• HAVING 里能写别名吗（HAVING done_cnt >= 3）？ MySQL 允许（它把 SELECT 别名带进了 HAVING），但标准 SQL 不允许，跨库移植会炸。稳妥写法是重复聚合表达式：HAVING COUNT(*) >= 3。

• WHERE 能用聚合吗？ 不能，执行顺序在聚合之前。想要「平均分大于 88 的课时」，条件属于组级，用 HAVING；想在 WHERE 里用子查询的聚合结果，那是子查询，第 10 课见。

• AVG 结果和手算不一致？ 先查 NULL：AVG 忽略 NULL，分母变小。再查整数除法：AVG 对整数列返回带小数的 DECIMAL，但 SUM/COUNT 组合自己算时 SUM/COUNT 是整数除法，要用 AVG 或 ROUND(SUM*1.0/COUNT, 2)。

• GROUP BY 之后行的顺序为什么看着没规律？ GROUP BY 不保证输出顺序。MySQL 8 不再隐式按分组键排序，需要顺序老老实实写 ORDER BY。

错法对照

错法	问题	正解
SELECT 非分组非聚合列	错误 1055（ONLY_FULL_GROUP_BY）	列进 GROUP BY，或套聚合
WHERE 里写聚合条件	语法/语义错	挪到 HAVING
COUNT(col) 当行数用	NULL 行被少算	数行数用 COUNT(*)
没有 GROUP BY 却写 HAVING	全表一组，多半是笔误	检查意图
按 TEXT 长列分组	临时表爆炸	用 id 分组再 JOIN 取文本
忽略 NULL 直接 AVG	平均分口径错误	先定 NULL 策略再聚合

卡点急救

1. 错误 1055 ...isn't in GROUP BY：把报错的列加进 GROUP BY，或给它套上聚合函数。别用 ANY_VALUE() 逃避，那是把不确定结果埋进系统。
2. 结果只有一行：忘了 GROUP BY，整表成了一组。
3. 分组结果行数不对：检查 WHERE 是否把行提前筛掉了（这常常正是想要的），以及分组键是否多了/少了列。
4. 想看每组里的明细：GROUP BY 只给组级结果；要明细+组信息是窗口函数或子查询的活，别硬拧。

面试常问

• WHERE 和 HAVING 的区别？ 执行时点不同：WHERE 在分组前筛行，不能用聚合；HAVING 在分组后筛组，能用聚合。能用 WHERE 先筛的数据不放 HAVING，减少分组量。

• COUNT(*)、COUNT(1)、COUNT(col) 的区别？ COUNT(*) 和 COUNT(1) 在 InnoDB 里等价（官方推荐 COUNT(*)，语义清晰且已专门优化）；COUNT(col) 只数 col 非 NULL 的行，语义不同。

• ONLY_FULL_GROUP_BY 是什么？为什么要开？ SQL 标准要求 SELECT 的非聚合列都在 GROUP BY 中。不开时 MySQL 会从组内「随手挑一行」的值，结果不确定；开启后把这类错误在开发期拦住。

• 统计每个部门工资最高的员工，怎么写？ 两种主流：相关子查询 WHERE (dept, salary) IN (SELECT dept, MAX(salary) … GROUP BY dept)；或窗口函数 ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) 取 rn=1。答出窗口函数是明显加分。

自测三问

1. COUNT(*)、COUNT(score)、COUNT(DISTINCT student_id) 在 study_record 上分别是多少？各数的是什么？
2. 写查询：每个城市的 VIP 学生数，只显示人数 >= 1 的城市。
3. 「平均分最高的已发布课时」和「每个分类平均分最高的课时」，哪个 GROUP BY 写不了？

小结一句话

聚合是「先分堆再算数」：COUNT 数行、SUM/AVG/MAX/MIN 算值，GROUP BY 定堆，HAVING 筛组；WHERE 管行、HAVING 管组，顺序由 FROM→WHERE→GROUP BY→HAVING→SELECT 决定。

延伸

• MySQL 聚合函数 / 窗口函数
• 下一课：INSERT、UPDATE、DELETE——从只读转向改数据，顺便学怎么不把库改坏。