📖 本篇简介：统计报表、分组排行是业务里的高频需求，也是 SQL 出"看似对、其实错"结果的重灾区。本章汇总聚合与分组方向最经典的 12 个坑，覆盖 ONLY_FULL_GROUP_BY、COUNT/AVG/SUM 对 NULL 的处理、HAVING、临时表、DISTINCT 计数、NULL 分组与分组列隐式转换。示例基于 MySQL 8.0，其中 NULL 聚合部分附真实运行的 Python/sqlite3 输出。读完你写的统计 SQL 才能真正"数对"。
目录
- 坑 1：GROUP BY 选了非聚合列
- 坑 2：COUNT(列) 和 COUNT(*) 不是一回事
- 坑 3：AVG 的分母不是全部行数
- 坑 4：整组 SUM 全为 NULL 返回 NULL 而不是 0
- 坑 5：把 HAVING 当 WHERE 用
- 坑 6：大表 GROUP BY 掉进临时表
- 坑 7：COUNT(DISTINCT) 慢
- 坑 8：分组列隐式类型转换
- 坑 9：把 GROUP BY 当分页用
- 坑 10：NULL 被归成一组
- 坑 11：多层 GROUP BY 叠加
- 坑 12：聚合函数里套 DISTINCT 误用
- 速查表：聚合分组速记
- 注意事项：统计 SQL 核对要点
- 自测：你能避坑吗
坑 1：GROUP BY 选了非聚合列
场景 / 错误示范：
-- 按部门分组，SELECT 里却带了 name（每个部门多人，name 取哪个？）
SELECT dept, name, COUNT(*)
FROM employee
GROUP BY dept;
-- 8.0 默认开启 ONLY_FULL_GROUP_BY，直接报错：
-- ERROR 1055: ... isn't contained in GROUP BY clause
为什么是坑：GROUP BY dept 后，每个 dept 只剩一行，但 name 在这组里有多个值，MySQL 不知道该返回哪个。5.7 之前会随便取一个，结果看似正常其实是错的。8.0 默认开启 ONLY_FULL_GROUP_BY 直接拒绝这种含糊查询，反而保护了你。
正确做法：
-- SELECT 里的非聚合列，必须都出现在 GROUP BY 里
SELECT dept, COUNT(*)
FROM employee
GROUP BY dept;
-- 想看到某个 name，就把它也加进 GROUP BY，或用聚合函数包起来
SELECT dept, MAX(name), COUNT(*) FROM employee GROUP BY dept;
加分点：别为了绕过报错去关 ONLY_FULL_GROUP_BY，那个报错正是在帮你查错。写出"分组后每行含义明确"的 SQL 才是正道。
坑 2：COUNT(列) 和 COUNT(*) 不是一回事
场景 / 错误示范：
-- 数订单数，写了 COUNT(amount)
SELECT COUNT(amount) FROM orders;
-- amount 为 NULL 的行被排除了！和 COUNT(*) 对不上
# sqlite3 实跑验证（MySQL 行为一致）：
# 表里 6 行，其中 3 行 amount 为 NULL
# COUNT(*) = 6, COUNT(amount) = 3
为什么是坑：COUNT(*) 数所有行，COUNT(列) 只数该列非 NULL 的行。很多人以为两者等价，统计"多少单"时用了 COUNT(可空列)，把没填金额的单漏数了，报表数字偏小。这是统计 SQL 最隐蔽的错。
正确做法：
-- 数"行数"一律用 COUNT(*)
SELECT COUNT(*) FROM orders;
-- 只有"数该列非空值的个数"时才用 COUNT(列)
SELECT COUNT(amount) FROM orders; -- 数有金额的单数
加分点：口诀——数行用 COUNT(*)，数列非空值用 COUNT(列)。报表对不上时，先查是不是用错了 COUNT。
坑 3：AVG 的分母不是全部行数
场景 / 错误示范：
-- 算用户平均订单金额，以为分母是"该用户全部订单数"
SELECT uid, AVG(amount) FROM orders GROUP BY uid;
-- 未支付订单 amount 为 NULL，AVG 只对非 NULL 求平均，分母比预期小
# sqlite3 实跑：
# uid=100 有 3 单，其中 1 单 amount 为 NULL
# COUNT(*)=3, COUNT(amount)=2, AVG(amount)=20.0
# 即 AVG = (10+30)/2 = 20，不是 (10+NULL+30)/3
为什么是坑：AVG 自动忽略 NULL，分母是"非 NULL 行数"而不是"分组总行数"。业务上"平均每个订单多少钱"应该把未支付订单也算进去（金额 0），结果 AVG 偷偷把它们排除了，平均值偏高。这是报表和真实感受对不上的经典原因。
正确做法：
-- 把 NULL 当 0 再求平均，分母就是全部行数
SELECT uid, AVG(IFNULL(amount, 0)) FROM orders GROUP BY uid;
-- 或明确业务口径：到底"已支付订单均价"还是"全部订单均价"，写清楚
SELECT uid, SUM(amount)/COUNT(*) AS avg_all FROM orders GROUP BY uid;
加分点：凡是聚合，都要想清楚 NULL 怎么处理。IFNULL/COALESCE 把 NULL 显式转成业务认可的值（通常是 0），口径才不会歪。
坑 4：整组 SUM 全为 NULL 返回 NULL 而不是 0
场景 / 错误示范：
-- 某个用户全是未支付订单，amount 全 NULL
SELECT uid, SUM(amount) FROM orders GROUP BY uid;
-- uid=200 那行：SUM(amount) = NULL，不是 0
# sqlite3 实跑：[(100, 40.0), (200, None), (300, 55.0)]
为什么是坑：SUM 对"全是 NULL"的组返回 NULL，不是 0。应用层拿到这个 NULL 做后续计算（比如 SUM 再除以某个值），就会出现空指针或结果莫名变 NULL。开发者以为空组会给 0，实际给了个 NULL。
正确做法：
-- 用 IFNULL 把空组兜底成 0
SELECT uid, IFNULL(SUM(amount), 0) AS total FROM orders GROUP BY uid;
-- 这样 uid=200 那行就是 0 而不是 NULL，应用层不用特判
加分点：凡是返回给前端/下游的聚合值，外层都包一层 IFNULL(..., 0)，把数据库的 NULL 兜底成业务认可的 0，省得下游到处判空。
坑 5：把 HAVING 当 WHERE 用
场景 / 错误示范：
-- 先过滤部门，再分组；却把过滤写进了 HAVING
SELECT dept, COUNT(*) FROM employee
GROUP BY dept
HAVING dept = '研发部';
-- 能跑，但 HAVING 是"分组后再过滤"，效率比 WHERE 差
为什么是坑：WHERE 在分组前过滤行，HAVING 在分组后过滤组。能用 WHERE 提前过滤的条件，放到 HAVING 就等于"先把全表都分组了再扔掉大部分组"，白做无用功。两者语义不同，混用会让本该高效的查询变慢。
正确做法：
-- 分组前就能确定的条件，放 WHERE
SELECT dept, COUNT(*) FROM employee
WHERE dept = '研发部' -- 分组前就过滤，只对研发部分组
GROUP BY dept;
-- HAVING 只用来过滤"聚合结果"
SELECT dept, COUNT(*) AS c FROM employee
GROUP BY dept
HAVING c > 100; -- 分组后才知道人数，只能放这
加分点：口诀——能 WHERE 不 HAVING。HAVING 是"筛组"，WHERE 是"筛行"，拿不准就想"这个条件在分组前能算吗"。
坑 6：大表 GROUP BY 掉进临时表
场景 / 错误示范：
-- 按一个没索引的列分组大表
SELECT category, COUNT(*) FROM huge_t GROUP BY category;
EXPLAIN: Extra = "Using temporary; Using filesort"
-- 建临时表做分组，内存装不下落磁盘，慢
为什么是坑：GROUP BY 若能用索引的有序性，就边扫边分；分组列没索引时，MySQL 要把结果集倒进临时表再分组排序。大结果集时临时表从内存转磁盘，IO 和 CPU 都吃紧。这是报表慢的常见根因。
正确做法：
-- 为分组列建索引，利用索引有序性免临时表
CREATE INDEX idx_category ON huge_t(category);
SELECT category, COUNT(*) FROM huge_t GROUP BY category;
-- Extra 里 temporary 消失
-- 实在要分组大表，加 WHERE 收窄参与分组的行数
SELECT category, COUNT(*) FROM huge_t WHERE create_time > '2026-01-01' GROUP BY category;
加分点：Using temporary 不是错误，但配合 Using filesort 和大结果集就要警惕。分组列有索引能显著加速聚合查询。
坑 7：COUNT(DISTINCT) 慢
场景 / 错误示范：
-- 数"有多少个不同用户下过单"
SELECT COUNT(DISTINCT user_id) FROM huge_order_t;
-- 大表上要对 user_id 去重计数，慢
为什么是坑：COUNT(DISTINCT col) 要在扫描过程中对 col 去重，需要维护一个去重集合（临时表）。数据量大时这个集合很占内存和 CPU。这是个"看起来简单、其实要全表去重"的查询。
正确做法：
-- 让它走索引：DISTINCT 列有索引时，去重能利用索引有序性
-- 建 idx_user_id，优化器能用松散索引扫描加速
-- 高频 UV 统计：别实时算，维护一张每日 UV 汇总表
-- 写入/定时任务里累加，查询直接读汇总，毫秒返回
SELECT uv FROM daily_uv WHERE dt = '2026-10-07';
加分点：UV、去重计数这类需求，实时算很贵。生产上几乎都是"预聚合+汇总表"模式，而不是现场 COUNT(DISTINCT)。
坑 8：分组列隐式类型转换
场景 / 错误示范：
-- user_id 是 VARCHAR，分组时传数字
SELECT user_id, COUNT(*) FROM t GROUP BY user_id;
-- 但关联的另一张表 user_id 是 BIGINT，分组/JOIN 时隐式转换，分组错乱或走不了索引
为什么是坑：分组列类型不一致（一边字符串一边数字），MySQL 隐式转换后分组，相同业务含义的 id 可能因为转换规则不同被分到不同组，统计结果对不上。和 JOIN 的隐式转换坑同源。
正确做法：
-- 分组列、关联列类型严格一致
ALTER TABLE t MODIFY user_id BIGINT; -- 和另一张表对齐
-- 分组/JOIN 前检查列类型
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.COLUMNS
WHERE TABLE_NAME='t' AND COLUMN_NAME='user_id';
加分点：表结构设计时就保证"同一个业务 id 在所有表里类型一致"，能同时避开 JOIN 失效和分组错乱两个坑。
坑 9：把 GROUP BY 当分页用
场景 / 错误示范：
-- 想取"每个用户最新的一条订单"，写错成
SELECT user_id, MAX(create_time) FROM order_t GROUP BY user_id;
-- 拿到了最新时间，但这条记录的其他字段（金额、状态）没取对
为什么是坑：GROUP BY + MAX 只给你"每组的最大时间值"，不会自动把那一行的其他列带出来。很多人想当然以为它会返回"最新那条订单的完整信息"，实际只能取到聚合值，其他列要么报错要么取错行。这是经典的"想用分组取 top1，结果取错"。
正确做法：
-- 取每组完整 top1，要用窗口函数或关联子查询
-- 方法一：关联子查询
SELECT o.* FROM order_t o
WHERE o.create_time = (
SELECT MAX(create_time) FROM order_t x WHERE x.user_id = o.user_id
);
-- 方法二（8.0）：窗口函数，更直观
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTY BY user_id ORDER BY create_time DESC) rn
FROM order_t
) t WHERE rn = 1;
加分点："每组 top N"是窗口函数的主场，详见窗口函数篇。别用 GROUP BY + MAX 硬凑完整行。
坑 10：NULL 被归成一组
场景 / 错误示范：
-- 按 city 分组，没填 city 的行聚成了 NULL 这一组
SELECT city, COUNT(*) FROM user GROUP BY city;
-- 结果里有一行 city=NULL，COUNT 是所有没填城市的用户
为什么是坑：GROUP BY 把所有 NULL 视为"同一组"（虽然 NULL = NULL 不为真，但分组时把 NULL 归并）。开发者没预料到会冒出一个 NULL 组，报表上出现个"空城市"分组很扎眼，下游展示也尴尬。
正确做法：
-- 把 NULL 归到一个明确的分类里
SELECT IFNULL(city, '未知') AS city, COUNT(*)
FROM user GROUP BY IFNULL(city, '未知');
-- 或在分组前过滤掉 NULL
SELECT city, COUNT(*) FROM user WHERE city IS NOT NULL GROUP BY city;
加分点：分组前先想"NULL 归哪一组"，用 IFNULL/COALESCE 把它显式映射到业务认可的"未知"类别，报表才不会冒出意外的 NULL 组。
坑 11：多层 GROUP BY 叠加
场景 / 错误示范：
-- 先按部门分组，再按岗位分组，嵌套写得很绕
SELECT dept, job, COUNT(*) FROM employee
GROUP BY dept, job;
-- 其实这是"两列联合分组"，不是"嵌套"，理解错了
为什么是坑：GROUP BY dept, job 是按 (dept, job) 组合分组，每个组合一行。很多人误以为是"先按 dept 分、组内再按 job 分"的层级结构，理解错导致统计口径错。多列分组是"笛卡尔组合"，不是嵌套树。
正确做法：
-- 理解：GROUP BY a, b = 每个 (a,b) 组合一行
SELECT dept, job, COUNT(*) FROM employee GROUP BY dept, job;
-- 想按部门小计+总计，用 WITH ROLLUP
SELECT dept, job, COUNT(*) FROM employee GROUP BY dept, job WITH ROLLUP;
加分点：需要"小计、合计"时用 WITH ROLLUP，它会在每个 dept 末尾加一行小计、最后加一行总计，比手工 UNION 方便。
坑 12：聚合函数里套 DISTINCT 误用
场景 / 错误示范：
-- 想数每个部门多少人，写成
SELECT dept, COUNT(DISTINCT name) FROM employee GROUP BY dept;
-- name 可能有重名，去重后人数偏少！
为什么是坑：COUNT(DISTINCT name) 是"去重后数 name 的个数"。用人名去重会把重名的两个人算成一个，人数统计偏小。DISTINCT 是"去重"，不是"计数"，套在错的列上就数错了。
正确做法：
-- 数人头就用 COUNT(*) 或 COUNT(主键)
SELECT dept, COUNT(*) FROM employee GROUP BY dept;
-- 真要"去重计数"，用唯一标识列
SELECT dept, COUNT(DISTINCT employee_id) FROM employee GROUP BY dept;
加分点：想清楚到底要"总行数"还是"某列去重后的个数"，别默认套 DISTINCT。去重计数的列必须是业务唯一标识。
速查表：聚合分组速记
| 写法 | 陷阱 | 正确做法 |
|---|---|---|
| GROUP BY 选非聚合列 | 报错或取错 | 非聚合列都进 GROUP BY |
| COUNT(可空列) | 漏数 NULL 行 | 数行用 COUNT(*) |
| AVG(可空列) | 分母不含 NULL | IFNULL(amount,0) 再平均 |
| SUM 空组 | 返回 NULL | IFNULL(SUM(...),0) |
| HAVING 筛普通条件 | 分组后才过滤 | 提前到 WHERE |
| 分组列无索引 | 临时表/filesort | 分组列建索引 |
| COUNT(DISTINCT) 实时算 | 慢 | 预聚合汇总表 |
| 分组列类型不一致 | 隐式转换错乱 | 类型对齐 |
| GROUP BY 取 top1 完整行 | 其他列取错 | 窗口函数/子查询 |
| NULL 分组 | 冒出 NULL 组 | IFNULL 映射"未知" |
| COUNT(DISTINCT 非唯一列) | 数偏少 | 去重列用唯一标识 |
注意事项：统计 SQL 核对要点
写完一条统计 SQL，先别急着上线，按下面几点核对口径。
1. COUNT 用法：数行还是数列非空值？用对了吗？
2. NULL 处理：AVG/SUM 把 NULL 算进去了吗？空组会不会返回 NULL？
3. 分组列：SELECT 的非聚合列都在 GROUP BY 里吗？
4. 过滤位置：能 WHERE 的条件提前了吗？
5. 去重口径：DISTINCT 用的列是业务唯一标识吗？
6. 结果抽验：挑一个小分组，手工数一遍对不对得上？
一句话：统计 SQL 最怕"看起来对、口径错"。每条都要想清楚"NULL 怎么处理、分母是多少、去重按哪列"，再交付。
补充提醒：上线一条统计 SQL 前，最好挑一个数据量小、能手工核对的分组，把 SQL 结果和人工数出来的对一遍。口径对不对，抽样比推理更可靠。
自测：你能避坑吗
题 1：COUNT(*) 和 COUNT(amount) 有什么区别？
<details>
<summary>参考答案</summary>
COUNT(*) 数所有行；COUNT(amount) 只数 amount 非 NULL 的行。实跑验证：6 行里 3 行 amount 为 NULL，COUNT(*)=6、COUNT(amount)=3。数行数一律用 COUNT(*)。
</details>
题 2：一个用户所有订单 amount 都是 NULL，SELECT SUM(amount) 返回什么？
<details>
<summary>参考答案</summary>
返回 NULL，不是 0（实跑验证 uid=200 的 SUM 为 None）。应用层拿到 NULL 再做计算会出问题。外层包 IFNULL(SUM(amount), 0) 兜底成 0。
</details>
题 3：WHERE dept='研发部' 和 HAVING dept='研发部' 有何不同？
<details>
<summary>参考答案</summary>
WHERE 在分组前过滤行，只对研发部做分组，高效；HAVING 在分组后过滤组，先把全表都分组再扔掉大部分，慢。分组前能确定的条件应放 WHERE，HAVING 只用来筛聚合结果。
</details>
题 4：想取"每个用户最新一条订单的完整信息"，用 GROUP BY user_id, MAX(create_time) 为什么不对？
<details>
<summary>参考答案</summary>
它只返回每组的最大时间值，带不出那一行的其他列（金额、状态会取错或报错）。取完整 top1 要用窗口函数 ROW_NUMBER() 或关联子查询，不能靠 GROUP BY+MAX 硬凑。
</details>
题 5：为什么 AVG(amount) 算出来的"平均订单金额"比业务预期偏高？
<details>
<summary>参考答案</summary>
AVG 自动忽略 NULL，未支付订单（amount 为 NULL）没进分母。实跑 uid=100 有 3 单、1 单 NULL，AVG=(10+30)/2=20 而不是 /3。应 AVG(IFNULL(amount,0)) 把未支付当 0，分母才是全部订单。
</details>
题 6：报表里突然多出一个 city=NULL 的分组，怎么来的？怎么处理？
<details>
<summary>参考答案</summary>
GROUP BY 会把所有 NULL 归为一组。用 IFNULL(city,'未知') 把 NULL 显式映射到"未知"类别，或分组前 WHERE city IS NOT NULL 过滤掉，报表就不会冒出意外的 NULL 组。
</details>
题 7：COUNT(DISTINCT name) 数出来的部门人数为什么偏少？
<details>
<summary>参考答案</summary>
name 可能有重名，DISTINCT 去重后把重名的两人算成一个。数人头应 COUNT(*) 或 COUNT(主键)；去重计数要选业务唯一标识（如 employee_id）。
</details>
题 8：EXPLAIN 里 GROUP BY 查询出现 "Using temporary; Using filesort"，说明什么？
<details>
<summary>参考答案</summary>
分组列没有可用索引，MySQL 只能把结果集倒进临时表再分组排序，大结果集时临时表会落磁盘变慢。为分组列建索引可利用索引有序性消除临时表。
</details>
题 9：为什么不要为了绕过 ONLY_FULL_GROUP_BY 报错去关这个开关？
<details>
<summary>参考答案</summary>
那个报错正是在阻止你写出"分组后取了不确定列"的错误 SQL。关掉它，5.7 之前那种"随便取一行 name"的隐患就回来了。正确做法是把 SELECT 的非聚合列都加进 GROUP BY 或用聚合函数包起来。
</details>
题 10：要做"每日 UV"统计，为什么不要每次都 COUNT(DISTINCT user_id) 现场算？
<details>
<summary>参考答案</summary>
大表上 COUNT(DISTINCT) 要全表去重、维护去重集合，慢。生产做法是预聚合：用定时任务/写入时累加维护一张每日 UV 汇总表，查询直接读汇总，毫秒返回。
</details>
下一篇预告：第 49 章 JOIN连接踩坑
文章回复
0 条公开回复