查询与聚合

这一课你将能做到

1. 背出并运用 SQL 的逻辑执行顺序（FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT）
2. 熟练写出 WHERE 行过滤 + GROUP BY 分组 + HAVING 筛组的报表查询，并解释为什么 WHERE COUNT(*) > 1 是语法错误
3. 区分 COUNT(*)、COUNT(列)、COUNT(DISTINCT 列) 的行为差异，正确计算 AC 率这类比例指标

为什么要学

报表和统计是后端日常工作的一半：运营要看每日提交量、面试官要你算「通过率最高的用户」，这些全是 GROUP BY + HAVING 的组合拳。更关键的是，only_full_group_by 默认开启后，很多网上抄来的 SQL 在 8.0 直接报错——你必须理解分组语义才能改对，而不是随手加 ANY_VALUE() 糊弄过去。

人话：WHERE 是「发试卷之前先筛掉不参加的人」，HAVING 是「考完之后只看平均分及格的班」。一个是行级过滤，一个是组级过滤，时机不同。

先建立图像

SQL 的逻辑执行顺序（注意这不是实际执行方式，而是语义上的求值顺序）：

FROM / JOIN   →  确定数据源
WHERE         →  逐行过滤（此时还没有"组"）
GROUP BY      →  把行折叠成组
HAVING        →  对组过滤
SELECT        →  计算输出列（聚合、别名在这里诞生）
ORDER BY      →  排序（可用 SELECT 的别名）
LIMIT         →  截取

为什么 WHERE 里不能用 SELECT 的别名、也不能用聚合函数？因为按顺序执行到 WHERE 时，SELECT 还没发生、组也还没形成。这一张图能解答你 80% 的报错。

跟着做

步骤 0：数据准备

USE sql_practice;
TRUNCATE TABLE cf_submission;
TRUNCATE TABLE cf_practice_record;
TRUNCATE TABLE cf_user;
TRUNCATE TABLE cf_problem;
-- 执行第 1 课步骤 4 的四段 INSERT，恢复到 6 用户 6 题 12 提交的初始状态

步骤 1：按难度统计提交量与 AC 数

SELECT
  p.difficulty,
  COUNT(*)                   AS total_sub,
  SUM(s.status = 'accepted') AS ac_cnt
FROM cf_submission s
JOIN cf_problem p ON p.id = s.problem_id
GROUP BY p.difficulty;

预期结果：

difficulty	total_sub	ac_cnt
easy	6	3
hard	1	1
medium	5	3

步骤 2：WHERE 与 HAVING 的分工

目标：找出 AC 率不低于 60% 的用户。

SELECT
  u.username,
  COUNT(s.id)                                   AS total_sub,
  SUM(s.status = 'accepted')                    AS ac_cnt,
  ROUND(SUM(s.status = 'accepted') / COUNT(s.id) * 100, 1) AS ac_rate
FROM cf_user u
JOIN cf_submission s ON s.user_id = u.id
GROUP BY u.id, u.username
HAVING ac_rate >= 60
ORDER BY ac_rate DESC, u.username;

预期：lin、zhao、zhou 三人（各 66.7%）。解读：WHERE 在分组前砍行（这里没用到，可以加 WHERE s.submitted_at < '2025-06-05' 感受变化），HAVING 在分组后砍组（过滤掉不达标的用户组）。qian 没出现在结果里是被 INNER JOIN 挤掉的（他没有任何提交），不是被 HAVING 过滤。

步骤 3：三种 COUNT 的差异

SELECT
  COUNT(*)                 AS rows_all,      -- 12：数行，不管 NULL
  COUNT(status)            AS rows_status,   -- 12：status 全 NOT NULL
  COUNT(DISTINCT user_id)  AS users_cnt,     -- 5 ：6 个用户只有 5 人提交过
  COUNT(DISTINCT problem_id) AS problems_cnt -- 5 ：6 道题只有 5 道被做过
FROM cf_submission;

再补一个 NULL 敏感实验：

SELECT SUM(runtime_ms) FROM cf_submission WHERE user_id = 6;
-- 结果 NULL（qian 没有提交，SUM 作用于空集返回 NULL，不是 0）

步骤 4：多列分组——按天统计

SELECT
  DATE(submitted_at) AS d,
  COUNT(*)           AS cnt
FROM cf_submission
GROUP BY DATE(submitted_at)
ORDER BY d;

预期：06-01 到 06-07 每天的提交数依次为 2、1、2、1、2、2、2。GROUP BY 后面写函数，SELECT 里也写同样的函数（only_full_group_by 要求输出列要么在 GROUP BY 里、要么被聚合包裹）。

步骤 5：ORDER BY 与 LIMIT

-- AC 提交里最快的 3 次
SELECT username, p.title, s.runtime_ms
FROM cf_submission s
JOIN cf_user u ON u.id = s.user_id
JOIN cf_problem p ON p.id = s.problem_id
WHERE s.status = 'accepted'
ORDER BY s.runtime_ms ASC, s.id ASC
LIMIT 3;
-- 预期前三：zhou/有效的括号/10ms、lin/两数之和/12ms、wang/两数之和/15ms

按 runtime 升序后 10ms、12ms、15ms 是最小的三个。多列排序（先按 ms 再按 id 决胜）让结果完全确定——没有唯一决胜列的 ORDER BY 在分页时会出现「翻页重复/丢失」。

讲清楚要点

1. 执行顺序决定一切语法合法性：WHERE 里不能用聚合和 SELECT 别名（组还不存在）；HAVING 里可以用聚合；ORDER BY 可以用 SELECT 别名（它在 SELECT 之后求值）。背住顺序图，报错自己就能诊断。
2. WHERE 过滤行，HAVING 过滤组：能用 WHERE 的条件绝不用 HAVING（先过滤减少参与分组的行，更快也更省内存）。HAVING 只放「必须看到组才能算」的条件，如 HAVING COUNT(*) > 1。
3. COUNT 家族：COUNT(*) 数行（含 NULL 行）、COUNT(列) 数该列非 NULL 的行、COUNT(DISTINCT 列) 数去重后的非 NULL 值。三者混用是统计对不上的常见原因。
4. 聚合对 NULL 的处理：SUM/AVG/MIN/MAX 自动忽略 NULL，作用于空集返回 NULL（不是 0）；SUM(status='accepted') 用布尔转 1/0 的技巧绕开了这个问题，配合 COALESCE(SUM(...), 0) 兜底最稳。
5. only_full_group_by 的语义：SELECT 里出现的每一列，要么出现在 GROUP BY 中，要么被聚合函数包裹。按主键分组（GROUP BY u.id, u.username）是标准放宽——主键决定其他列的值，语义安全。
6. 分页要有确定性：ORDER BY 带上唯一列（如 id）做决胜，否则同值行的顺序不稳定，深分页会重复或漏行；深分页本身在第 11 课展开。

你可能会懵的地方

• HAVING 后面能用 SELECT 的别名吗？ MySQL 扩展允许（HAVING 在 SELECT 附近求值，如 HAVING ac_rate >= 60）；但标准 SQL 不允许，其他数据库不兼容。团队规范建议写完整表达式，本课示例为了可读性用了别名。

• GROUP BY 一个列都没写、直接 SELECT COUNT(*) 行不行？ 行。整表被当成一组，返回一行。同理 SELECT MAX(runtime_ms) FROM cf_submission WHERE ... 也是隐式分组。

• 为什么 AVG(runtime_ms) 出来一长串小数？ AVG 返回精确小数（DECIMAL 运算）。报表场景用 ROUND(AVG(x), 1) 收口，或 CAST 成 DECIMAL 固定位数。

• GROUP BY 之后结果顺序乱了？ 8.0 的 GROUP BY 不再隐式排序（5.7 时代会偷偷按分组列排）。想要顺序必须显式 ORDER BY——依赖隐式排序的旧 SQL 迁移到 8.0 会翻车。

• HAVING 不配合 GROUP BY 写合法吗？ 合法，语义是「把全表当一组，筛这一组」。SELECT COUNT(*) FROM t HAVING COUNT(*) > 100 会在空表时一行都不返回，常用来做总量校验。

错法对照

错法	问题	正解
WHERE COUNT(*) > 1	WHERE 阶段组还没形成	HAVING COUNT(*) > 1
GROUP BY 漏列导致 ERROR 1055	only_full_group_by 拒绝歧义列	补全 GROUP BY 列，或显式聚合/ANY_VALUE
WHERE YEAR(created_at)=2025	对列套函数，索引失效	范围写法 WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'
分页 ORDER BY 不带决胜列	翻页出现重复/漏行	ORDER BY runtime_ms, id
统计人数用 COUNT(可空列)	NULL 行被漏掉	数行数用 COUNT(*) 或 COUNT(主键)

卡点急救

1. ERROR 1055 Expression of SELECT list is not in GROUP BY：把 SELECT 里的裸列补进 GROUP BY（按主键分组即可顺带覆盖其他列），或确认该列真的该被聚合。
2. 结果顺序不稳定：任何依赖顺序的查询都补显式 ORDER BY + 唯一决胜列。
3. HAVING 里的别名报 Unknown column（其他数据库或严格模式）：把别名还原成完整表达式，如 HAVING SUM(s.status='accepted')/COUNT(s.id) >= 0.6。

面试常问

• WHERE 和 HAVING 的区别？ WHERE 在分组前过滤行，不能用聚合函数；HAVING 在分组后过滤组，可以用聚合。性能上优先 WHERE（减少参与分组的行）。

• COUNT(*)、COUNT(1)、COUNT(列) 的区别？ InnoDB 里 COUNT() 与 COUNT(1) 等价（8.0 对空表做了专门优化，COUNT() 最规范）；COUNT(列) 只数该列非 NULL 的行，语义不同。

• SQL 的逻辑执行顺序？ FROM/JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT。答出「WHERE 时组不存在所以不能用聚合」是理解而非背诵的证明。

自测三问

1. 找出提交量大于 1 的用户，条件应该写在 WHERE 还是 HAVING？为什么？
2. SUM(status='accepted') 与 COUNT(CASE WHEN status='accepted' THEN 1 END) 等价吗？空集时分别返回什么？
3. 为什么 GROUP BY 主键可以合法地带出其他列？

小结一句话

聚合查询的灵魂是执行顺序：WHERE 砍行、GROUP BY 折叠、HAVING 筛组、ORDER BY 定序——顺序懂了，90% 的分组报错都能自己修。

延伸

• MySQL 8.0 Reference Manual — Aggregate Functions
• MySQL 8.0 Reference Manual — GROUP BY Handling
• 下一课：内置函数——字符串、日期、CASE 与空值处理，把清洗逻辑留在数据库层。