第 12 章聚合与分组面试精讲

站长2026-10-31 16:00:00

内容摘要

聚合与分组是 SQL 最基础也最容易踩坑的部分，看似简单，实则在面试中能衍生出 GROUP BY 严格模式、WHERE 与 HAVING 区别、COUNT(*) 与 COUNT(列) 差异、NULL 在聚合中的行为、ROLLUP/CUBE、GROUPING SETS、并行聚合等大量考点。本篇基于 MySQL 8.0，系统梳理常用聚合函数、分组规则、ONLY_FULL_GROUP_BY 模式、WITH ROLLUP、GROUP_CONCAT、BIT 聚合等高频知识点，并通过真实案例讲清楚"为什么我 GROUP BY 后查的列报错"。学完后你能避开绝大多数聚合类面试坑。

📖 本篇简介：聚合与分组是 SQL 最基础也最容易踩坑的部分，看似简单，实则在面试中能衍生出 GROUP BY 严格模式、WHERE 与 HAVING 区别、COUNT(*) 与 COUNT(列) 差异、NULL 在聚合中的行为、ROLLUP/CUBE、GROUPING SETS、并行聚合等大量考点。本篇基于 MySQL 8.0，系统梳理常用聚合函数、分组规则、ONLY_FULL_GROUP_BY 模式、WITH ROLLUP、GROUP_CONCAT、BIT 聚合等高频知识点，并通过真实案例讲清楚"为什么我 GROUP BY 后查的列报错"。学完后你能避开绝大多数聚合类面试坑。

题目 1：WHERE 与 HAVING 的区别，能否互换
题目 2：COUNT(*)、COUNT(1)、COUNT(列) 三者区别与性能
题目 3：ONLY_FULL_GROUP_BY 是什么，为什么 5.7 默认开启
题目 4：GROUP BY 后 SELECT 非聚合列为什么报错
题目 5：SUM、AVG、MAX、MIN 对 NULL 的处理
题目 6：COUNT(*) 遇到 NULL 行会怎么算
题目 7：GROUP BY 能否使用索引，什么情况下会 filesort
题目 8：WITH ROLLUP 的作用，MySQL 8.0 有什么增强
题目 9：GROUP_CONCAT 的作用、长度限制与坑
题目 10：DISTINCT 与 GROUP BY 去重性能谁好
题目 11：聚合函数能否嵌套，比如 SUM(AVG(x))
题目 12：GROUP BY 多个列的语义是什么
题目 13：HAVING 中能否使用 SELECT 别名
题目 14：BIT_AND、BIT_OR、BIT_XOR 聚合函数的用途
题目 15：如何在分组后筛选"组内满足条件的行数"
题目 16：分组查询慢，有哪些优化手段
题目 17：MySQL 8.0 支持 GROUPING SETS 吗，怎么替代
题目 18：STDDEV、VARIANCE 等统计聚合函数什么时候用
题目 19：聚合查询返回空结果时 COUNT 的行为
题目 20：常见坑与注意事项
速查表：聚合函数速记
复习清单
练习题

题目 1：WHERE 与 HAVING 的区别，能否互换

考点

执行时机不同：WHERE 在分组前，HAVING 在分组后
WHERE 不能用聚合函数，HAVING 可以
性能差异：提前过滤减少分组数据量

参考答案

1. WHERE：在分组/聚合之前过滤行，不能使用聚合函数，作用于原始行。

2. HAVING：在 GROUP BY 分组之后过滤组，可以使用聚合函数，作用于分组结果。

3. 执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。

4. 性能原则：能用 WHERE 过滤的就不要放到 HAVING，提前过滤能减少分组的数据量。

-- 正确：WHERE 先过滤 2026 年的数据，再按部门分组
SELECT dept, COUNT(*) FROM emp
WHERE hire_date >= '2026-01-01'
GROUP BY dept
HAVING COUNT(*) > 5;   -- HAVING 过滤人数大于 5 的部门

追问

追问：HAVING 后不写 GROUP BY 行不行？应对：可以，整个结果集当成一个大组，但很少这么用。
追问：WHERE 里能用聚合函数吗？应对：不能，因为执行顺序在分组之前。

题目 2：COUNT(*)、COUNT(1)、COUNT(列) 三者区别与性能

考点

COUNT(*) 统计所有行含 NULL
COUNT(列) 排除该列为 NULL 的行
InnoDB 下三者性能几乎无差别

参考答案

1. COUNT(*)：统计结果集的所有行，不忽略 NULL，是 SQL 标准统计行数的写法。

2. COUNT(1)：与 COUNT(*) 等价，InnoDB 优化器会把它当成 COUNT(*) 处理，结果完全一样。

3. COUNT(列名)：统计该列不为 NULL 的行数；列中有 NULL 时结果小于 COUNT(*)。

4. 性能：InnoDB 下 COUNT(*)、COUNT(1)、COUNT(主键) 性能几乎无差别；MyISAM 无 WHERE 时 COUNT(*) 是 O(1)，因为有元数据缓存。

-- 假设 emp 有 1000 行，其中 manager 列有 50 行 NULL
SELECT COUNT(*),          -- 1000
       COUNT(1),          -- 1000
       COUNT(manager)     -- 950
FROM emp;

追问

追问：COUNT(*) 在 InnoDB 下为什么慢？应对：InnoDB 要遍历索引或数据页数行，没有元数据缓存；大表要做近似计数用 show table status 或外部缓存。
追问：COUNT(主键) 比 COUNT(*) 快吗？应对：在 InnoDB 下优化器选最小的索引扫描，差异极小，别纠结。

题目 3：ONLY_FULL_GROUP_BY 是什么，为什么 5.7 默认开启

考点

SQL 标准要求 SELECT 列要么在 GROUP BY 中，要么被聚合
5.7 前默认不开启，导致结果不确定
8.0 延续默认开启

参考答案

1. ONLY_FULL_GROUP_BY：MySQL 的 SQL 模式之一，开启后要求 SELECT 列表中的非聚合列必须全部出现在 GROUP BY 子句中，否则报错。

2. 为什么开启：在未开启时，SELECT dept, name, COUNT(*) FROM emp GROUP BY dept 这种语句能执行，但 name 取哪个部门的"任意一行"是不确定的，结果随数据顺序变化。

3. MySQL 5.7 起默认开启该模式，8.0 延续；这是对 SQL 标准的对齐。

4. 临时关闭（不推荐）：SET SESSION sql_mode = (REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));。

追问

追问：业务代码在 5.6 升到 8.0 后报 GROUP BY 错误怎么办？应对：检查 SQL，把非聚合列加进 GROUP BY，或用 ANY_VALUE() 包起来（明确告诉 MySQL 取任意值）。
追问：ANY_VALUE() 是什么？应对：8.0 提供的函数，包住某列表示"我知道取值不确定，取一行就行"，用于绕过 ONLY_FULL_GROUP_BY。

题目 4：GROUP BY 后 SELECT 非聚合列为什么报错

考点

ONLY_FULL_GROUP_BY 规则
函数依赖（Functional Dependency）例外
正确写法

参考答案

-- 错误：name 既不在 GROUP BY 中，也没有聚合
SELECT dept, name, COUNT(*) FROM emp GROUP BY dept;
-- ERROR 1055: Expression #2 of SELECT list is not in GROUP BY clause...

-- 正确 1：把 name 加进 GROUP BY
SELECT dept, name, COUNT(*) FROM emp GROUP BY dept, name;

-- 正确 2：用聚合函数包起来
SELECT dept, MAX(name), COUNT(*) FROM emp GROUP BY dept;

-- 正确 3：用 ANY_VALUE 明确接受任意值
SELECT dept, ANY_VALUE(name), COUNT(*) FROM emp GROUP BY dept;

1. 报错是因为 MySQL 不知道在该部门的多行 name 中要返回哪一个。

2. 函数依赖例外：如果 GROUP BY 的是主键（如 GROUP BY emp_id），那么 SELECT 其他列（name、dept）是允许的，因为主键唯一确定一行。

3. 业务上真的要"每个部门随便挑一个员工名"，用 ANY_VALUE。

追问

追问：GROUP BY 主键后 SELECT 其他列为啥不报错？应对：主键函数依赖，MySQL 能证明其他列唯一确定。
追问：GROUP BY 唯一索引列也一样吗？应对：是的，只要列上有 NOT NULL 的唯一约束即可。

题目 5：SUM、AVG、MAX、MIN 对 NULL 的处理

考点

聚合函数自动忽略 NULL
AVG 是 SUM/非NULL行数，不是除以总行数
全 NULL 时返回 NULL

参考答案

1. SUM/AVG/MAX/MIN：自动忽略 NULL 行，只对非 NULL 值计算。

2. AVG 的坑：AVG(col) = SUM(col) / COUNT(col)，分母是"非 NULL 行数"，不是总行数。

3. 全 NULL 输入：SUM/AVG 返回 NULL（不是 0），MAX/MIN 也返回 NULL。

4. COUNT(*) 例外：不忽略任何行。

-- 假设 score 列值为 (10, 20, NULL)
SELECT SUM(score),   -- 30
       AVG(score),   -- 15 （不是 (10+20+0)/3=10）
       COUNT(score), -- 2
       COUNT(*)      -- 3
FROM t;

追问

追问：AVG 想把 NULL 当 0 算怎么办？应对：AVG(IFNULL(score,0))。
追问：SUM 全 NULL 返回什么？应对：NULL，不是 0；要 0 用 COALESCE(SUM(x),0)。

题目 6：COUNT(*) 遇到 NULL 行会怎么算

考点

COUNT(*) 不忽略 NULL
COUNT(列) 忽略该列 NULL
业务中常用来统计"有多少订单"

参考答案

1. COUNT(*)：统计所有行，不论列是否为 NULL，行数就是行数。

2. COUNT(列名)：只统计该列 IS NOT NULL 的行。

3. COUNT(DISTINCT 列)：统计该列去重后的非 NULL 值个数。

-- order 表 5 行，其中 pay_time 列 2 行是 NULL
SELECT COUNT(*),           -- 5
       COUNT(pay_time),     -- 3
       COUNT(DISTINCT user_id) -- 用户去重数
FROM orders;

追问

追问：COUNT(1) 和 COUNT(*) 有区别吗？应对：InnoDB 下没有，等价。
追问：要统计"有支付的订单数"用哪个？应对：COUNT(pay_time) 或 COUNT(*) WHERE pay_time IS NOT NULL，后者可读性更好。

题目 7：GROUP BY 能否使用索引，什么情况下会 filesort

考点

最左前缀原则
索引顺序避免临时表与排序
EXPLAIN 的 Extra 字段

参考答案

1. 能用到索引：GROUP BY 的列顺序与联合索引最左前缀一致，且索引有序，MySQL 可直接利用索引顺序分组，避免临时表和 filesort。

2. filesort 出现：GROUP BY 列与索引顺序不匹配，或出现 ORDER BY 不同方向，MySQL 建临时表做分组。

3. EXPLAIN 看 Extra：

Using index：覆盖索引，最佳。
Using temporary; Using filesort：分组产生临时表+排序，需优化。

-- 联合索引 idx_dept_hire(dept, hire_date)
-- 下面这条 GROUP BY 能利用索引顺序
SELECT dept, YEAR(hire_date), COUNT(*)
FROM emp GROUP BY dept, YEAR(hire_date);  -- YEAR() 函数让索引失效，会 filesort

-- 想利用索引，GROUP BY 列要与索引列完全一致
SELECT dept, hire_date, COUNT(*) FROM emp GROUP BY dept, hire_date;

追问

追问：GROUP BY 默认会排序吗？应对：8.0 之前 GROUP BY 隐式排序，8.0 取消了隐式排序，要排序必须显式 ORDER BY。
追问：怎样去掉 GROUP BY 的排序开销？应对：8.0 默认不排序；旧版本加 ORDER BY NULL。

题目 8：WITH ROLLUP 的作用，MySQL 8.0 有什么增强

考点

生成多级小计与合计行
GROUPING() 函数区分超级聚合行
8.0 支持 ORDER BY 与 ROLLUP 共存

参考答案

-- 按部门 + 年份分组，并自动生成小计与总计
SELECT dept, YEAR(hire_date) AS yr, COUNT(*)
FROM emp
GROUP BY dept, YEAR(hire_date) WITH ROLLUP;
-- 结果会多出：每个部门一行 dept=部门, yr=NULL 的小计；最后一行 dept=NULL,yr=NULL 的总计

1. 作用：在 GROUP BY 结果基础上，自动生成各层级的小计与最终合计行，无需 UNION ALL。

2. NULL 表示超级聚合行：ROLLUP 产生的小计行中，被"汇总掉"的列会显示 NULL，容易与业务数据本身的 NULL 混淆。

3. GROUPING(列)：8.0 提供，返回 1 表示该行是 ROLLUP 产生的超级聚合行，0 表示普通分组行。

4. 8.0 增强：支持 ROLLUP 与 ORDER BY 共存（之前版本互斥），并支持 GROUP BY ... WITH ROLLUP 之外的显式 ROLLUP() 语法。

追问

追问：ROLLUP 能按任意层级聚合吗？应对：MySQL 只支持从右到左逐级回退，不支持 GROUPING SETS 那种任意组合。
追问：要多级小计但不想从右到左怎么办？应对：用 UNION ALL 拼接多个 GROUP BY，或迁到支持 GROUPING SETS 的引擎。

题目 9：GROUP_CONCAT 的作用、长度限制与坑

考点

把分组多行拼成一个字符串
group_concat_max_len 限制
默认分隔符与排序

参考答案

-- 每个部门的员工名字拼成逗号分隔字符串
SELECT dept, GROUP_CONCAT(name ORDER BY salary DESC SEPARATOR '、') AS members
FROM emp
GROUP BY dept;

1. 作用：把分组内多行的某列拼成一个字符串，常用于"一个部门有哪些人"。

2. 长度限制：受 group_concat_max_len 系统变量控制，默认 1024 字节，超长会被静默截断！线上要注意。

3. 默认分隔符：逗号 ,；可通过 SEPARATOR 修改。

4. 支持 ORDER BY：在聚合内排序拼接。

-- 查看/调整长度限制
SHOW VARIABLES LIKE 'group_concat_max_len';
SET SESSION group_concat_max_len = 102400;  -- 会话级调大

追问

追问：拼出来有 NULL 怎么办？应对：GROUP_CONCAT 自动忽略 NULL，与 SUM 一致。
追问：线上拼出来字符串被截断查不到原因？应对：先查 group_concat_max_len，再看结果长度是否超限。

题目 10：DISTINCT 与 GROUP BY 去重性能谁好

考点

去重本质都是排序/临时表
有索引时两者差不多
GROUP BY 能顺带聚合更灵活

参考答案

1. DISTINCT col 与 GROUP BY col 去重语义等价：都是把重复行折叠。

2. 性能：在有合适索引时，两者优化器生成的执行计划几乎一样，都走索引扫描或临时表。

3. 选择建议：

只去重不聚合：用 DISTINCT，语义清晰。
要顺便做 COUNT/SUM 等聚合：用 GROUP BY。

4. DISTINCT 多列：SELECT DISTINCT a,b FROM t 是按 (a,b) 组合去重。

-- 两种写法等价
SELECT DISTINCT dept FROM emp;
SELECT dept FROM emp GROUP BY dept;

追问

追问：DISTINCT 能不能作用在部分列？应对：不能，DISTINCT 作用于整行 SELECT 列表；要对某列去重用 GROUP BY。
追问：COUNT(DISTINCT col) 性能？应对：需要对该列去重计数，数据量大时会产生临时表，比 COUNT(*) 慢。

题目 11：聚合函数能否嵌套，比如 SUM(AVG(x))

考点

SQL 标准不允许聚合嵌套
解决：包子查询/CTE
窗口函数也不能嵌套

参考答案

1. 不能直接嵌套：SELECT SUM(AVG(salary)) FROM emp GROUP BY dept 报语法错误。

2. 原因：聚合函数是对行集计算，SQL 标准不允许一层 SELECT 里同时嵌套两层聚合。

3. 正确做法：先内层 GROUP BY 算出每个 dept 的 AVG，外层再 SUM：

-- 错误：SELECT SUM(AVG(salary)) FROM emp GROUP BY dept;
-- 正确：
SELECT AVG(dept_avg) AS avg_of_dept_avg
FROM (
  SELECT dept, AVG(salary) AS dept_avg FROM emp GROUP BY dept
) t;

追问

追问：那 SUM(MAX(x)) 呢？应对：同样不行，必须包两层。
追问：HAVING 里能写 COUNT(*) > COUNT(DISTINCT user_id) 吗？应对：这种同层两个聚合是允许的，因为它们并列而不是嵌套。

题目 12：GROUP BY 多个列的语义是什么

考点

按列组合分组
等价于 (col1,col2) 相同的行归为一组
顺序影响可读性不影响结果

参考答案

1. 语义：GROUP BY a, b 把 (a,b) 值相同的行分到同一组；NULL 与 NULL 在分组中视为相等。

2. 行数：结果行数 = SELECT COUNT(DISTINCT a, b) FROM t。

3. 顺序：GROUP BY a,b 与 GROUP BY b,a 结果集相同（不考虑排序），但索引利用可能不同。

4. 搭配 ROLLUP：GROUP BY a,b WITH ROLLUP 会先按 a,b 分组，再生成 a 的小计，最后总计。

-- 按部门 + 岗位分组统计人数
SELECT dept, job, COUNT(*) FROM emp GROUP BY dept, job;

追问

追问：GROUP BY a,b 时 a 相同但 b 不同会分一组吗？应对：不会，必须 (a,b) 都相同才一组。
追问：多列 GROUP BY 怎么建索引？应对：联合索引列顺序与 GROUP BY 一致，最左前缀。

题目 13：HAVING 中能否使用 SELECT 别名

考点

MySQL 扩展支持 HAVING 引用别名
WHERE 不支持
ORDER BY 也支持

参考答案

1. MySQL 特有扩展：MySQL 允许 HAVING、ORDER BY 引用 SELECT 列表中定义的别名，这在 Oracle/PostgreSQL 中不允许。

2. WHERE 不允许：因为 WHERE 执行早于 SELECT。

3. 示例：

SELECT dept, AVG(salary) AS avg_sal
FROM emp
GROUP BY dept
HAVING avg_sal > 10000;   -- MySQL 允许引用别名

4. 可移植性建议：写 HAVING AVG(salary) > 10000 更标准，跨数据库兼容。

追问

追问：WHERE 中能用别名吗？应对：不能，MySQL 也不支持。
追问：ORDER BY 能用别名吗？应对：可以，MySQL 与 SQL 标准都允许。

题目 14：BIT_AND、BIT_OR、BIT_XOR 聚合函数的用途

考点

位运算聚合
权限位、开关位合并
与常规聚合区别

参考答案

1. BIT_AND / BIT_OR / BIT_XOR：对分组内每行的某列做按位与/或/异或聚合。

2. 典型场景：用户权限位合并——每个用户在某个资源上有一组权限位，要算整个部门的权限并集，用 BIT_OR。

3. 示例：

-- 每个部门的权限位并集（位或）
SELECT dept, BIT_OR(permission) AS dept_perm
FROM user_role
GROUP BY dept;

-- 判断部门是否有人同时具备 bit0 和 bit1：BIT_AND(permission) & 3 = 3
SELECT dept, BIT_AND(permission) AS all_have
FROM user_role GROUP BY dept;

追问

追问：BIT_XOR 有什么用？应对：找"出现奇数次的位"，类似校验和。
追问：空组返回什么？应对：BIT_AND 全 1、BIT_OR 全 0、BIT_XOR 0。

题目 15：如何在分组后筛选"组内满足条件的行数"

考点

HAVING COUNT(CASE WHEN ...)
条件聚合
行转列

参考答案

-- 求每个部门中工资 > 10000 的员工人数，并筛出至少 3 人的部门
SELECT dept,
       COUNT(*) AS total,
       SUM(CASE WHEN salary > 10000 THEN 1 ELSE 0 END) AS high_cnt
FROM emp
GROUP BY dept
HAVING high_cnt >= 3;

1. 思路：用 SUM(CASE WHEN 条件 THEN 1 ELSE 0 END) 做条件计数。

2. 这就是经典的"行转列"：把每个分组内满足不同条件的行数作为不同列。

3. 比 COUNT(*) WHERE salary>10000 GROUP BY dept 更灵活，后者只能算一个指标。

追问

追问：SUM(CASE...) 和 COUNT(IF...) 哪个好？应对：等价，COUNT(IF(cond,1,NULL)) 也行，前者更标准。
追问：要同时统计多个条件怎么办？应对：写多个 SUM(CASE WHEN...) 列即可。

题目 16：分组查询慢，有哪些优化手段

考点

索引覆盖分组列
提前 WHERE 过滤
减少返回行数
临时表调优

参考答案

1. 建合适索引：(GROUP BY 列, ...) 联合索引，让分组利用索引顺序，避免 filesort。

2. WHERE 提前过滤：把能在分组前过滤的条件都放到 WHERE，减少分组数据量。

3. 只 SELECT 需要的列：避免 SELECT *，减少临时表内存占用。

4. 调大临时表参数：tmp_table_size、max_heap_table_size 适当调大，避免落盘。

5. 预聚合：把高频分组结果物化成汇总表/统计表，用空间换时间。

6. EXPLAIN 验证：看 Extra 是否还有 Using temporary; Using filesort。

追问

追问：临时表落盘怎么发现？应对：看 Created_tmp_disk_tables 状态变量与 Created_tmp_tables 比值，大就说明落盘多。
追问：大表分组聚合怎么扛？应对：汇总表 + 定时任务刷新，或者引入 OLAP 引擎（ClickHouse）。

题目 17：MySQL 8.0 支持 GROUPING SETS 吗，怎么替代

考点

GROUPING SETS 是 OLAP 标准
MySQL 不支持
用 UNION ALL 替代

参考答案

1. MySQL 8.0 不支持 GROUPING SETS / CUBE，只支持 WITH ROLLUP。

2. GROUPING SETS：可以一次查询同时返回按 (a)、按 (b)、按 (a,b)、总计多种粒度的聚合。

3. 替代写法：用 UNION ALL 拼多个 GROUP BY：

-- 等价于 GROUPING SETS ((dept, yr), (dept), (yr), ())
SELECT dept, YEAR(hire_date) AS yr, COUNT(*) FROM emp GROUP BY dept, YEAR(hire_date)
UNION ALL
SELECT dept, NULL, COUNT(*) FROM emp GROUP BY dept
UNION ALL
SELECT NULL, YEAR(hire_date), COUNT(*) FROM emp GROUP BY YEAR(hire_date)
UNION ALL
SELECT NULL, NULL, COUNT(*) FROM emp;

4. 这种写法冗长但能跑；更复杂的多维分析建议迁到 ClickHouse/Doris。

追问

追问：WITH ROLLUP 与 GROUPING SETS 区别？应对：ROLLUP 是固定从右到左层级回退，GROUPING SETS 可任意组合维度。
追问：什么时候该考虑 OLAP 库？应对：报表/BI 查询多维度聚合、单表千万级以上、MySQL 分组扛不住时。

题目 18：STDDEV、VARIANCE 等统计聚合函数什么时候用

考点

标准差、方差
变异系数
业务场景

参考答案

1. STDDEV_POP / STDDEV_SAMP：总体/样本标准差。

2. VAR_POP / VAR_SAMP：总体/样本方差。

3. 业务场景：

监控接口响应时间的波动：STDDEV 大说明不稳定。
评估薪资离散程度：变异系数 = STDDEV/AVG。

4. 这些函数日常业务用得少，但面试中提到"统计聚合"会加分。

-- 各部门工资的均值与标准差
SELECT dept,
       AVG(salary)    AS avg_sal,
       STDDEV_POP(salary) AS std_sal
FROM emp GROUP BY dept;

追问

追问：STDDEV 与 STDDEV_POP 区别？应对：STDDEV 是 STDDEV_POP 的别名，8.0 推荐显式写 _POP/_SAMP。
追问：空结果返回什么？应对：NULL。

题目 19：聚合查询返回空结果时 COUNT 的行为

考点

无 GROUP BY 的聚合总是返回一行
有 GROUP BY 时可能返回 0 行
COUNT 空表返回 0，SUM 返回 NULL

参考答案

-- 无 GROUP BY：即使表空，也返回一行
SELECT COUNT(*), SUM(salary) FROM emp WHERE 1=2;
-- 结果：COUNT(*)=0, SUM(salary)=NULL

-- 有 GROUP BY：没有分组匹配时返回 0 行
SELECT dept, COUNT(*) FROM emp WHERE 1=2 GROUP BY dept;
-- 结果：空结果集（0 行）

1. 无 GROUP BY 的聚合：永远返回一行，COUNT 是 0，SUM/AVG 是 NULL。

2. 有 GROUP BY 的聚合：没有行匹配 WHERE 时返回空结果集（0 行），应用层要注意判空。

3. 业务坑：代码里 rs.next() 后直接取第一行，有 GROUP BY 但无分组数据时会抛 NoSuchElementException。

追问

追问：SUM 空表返回 NULL 还是 0？应对：NULL；要 0 用 COALESCE。
追问：怎么让分组查询无数据时也返回一行？应对：外层包 SELECT ... FROM (SELECT 1) dummy LEFT JOIN (...) t。

题目 20：常见坑与注意事项

考点

聚合高频坑汇总
NULL 与分组
隐式排序变化

参考答案

1. 坑 1：AVG 自动忽略 NULL，分母不是总行数。

2. 坑 2：GROUP_CONCAT 被 group_concat_max_len 静默截断，线上拼字符串超长要先查参数。

3. 坑 3：5.7/8.0 默认 ONLY_FULL_GROUP_BY，老 SQL 升级后报错，要改 SQL 或用 ANY_VALUE。

4. 坑 4：GROUP BY 在 8.0 不再隐式排序，旧代码依赖隐式排序会出问题。

5. 坑 5：COUNT(列) 与 COUNT(*) 不等价，列有 NULL 时结果不同。

6. 坑 6：聚合函数不能嵌套，要多层聚合必须包子查询。

7. 坑 7：HAVING 引用别名是 MySQL 扩展，跨库移植时要改回完整聚合表达式。

8. 坑 8：WITH ROLLUP 产生的 NULL 与业务 NULL 混淆，用 GROUPING() 区分。

追问

追问：升级到 8.0 后 GROUP BY 结果顺序变了？应对：8.0 去掉了隐式排序，业务必须显式 ORDER BY。
追问：HAVING 里写 WHERE 条件性能差？应对：能用 WHERE 就放 WHERE，提前过滤减少分组开销。

速查表：聚合函数速记

| 函数 | 作用 | 忽略 NULL | 空结果返回 |

|------|------|-----------|-----------|

| COUNT(*) | 统计行数 | 否 | 0 |

| COUNT(列) | 统计非 NULL 行数 | 是 | 0 |

| SUM(列) | 求和 | 是 | NULL |

| BIT_AND/OR/XOR | 位聚合 | 是 | 全1/全0/0 |

| 写法 | 语义 |

|------|------|

| WHERE | 分组前过滤，不能用聚合 |

| HAVING | 分组后过滤，能用聚合 |

| GROUP BY a,b | 按 (a,b) 组合分组 |

| WITH ROLLUP | 自动生成小计/总计 |

| DISTINCT | 对整行 SELECT 列表去重 |

复习清单

| 题目 | 核心结论 | 易错点 |

|------|----------|--------|

| 1 | WHERE 在分组前，HAVING 在分组后 | 把过滤条件都堆到 HAVING |

| 2 | COUNT(列) 忽略 NULL，COUNT(*) 不忽略 | 误以为 COUNT(1) 更快 |

| 3 | 5.7+ 默认 ONLY_FULL_GROUP_BY | 升级后 SQL 报错不修 |

| 4 | 非聚合列必须在 GROUP BY 中 | 主键函数依赖例外不知道 |

| 5 | AVG 分母是非 NULL 行数 | 想把 NULL 当 0 忘写 IFNULL |

| 6 | COUNT(*) 不忽略 NULL | COUNT(列) 统计有值行数 |

| 7 | 联合索引可避免 filesort | GROUP BY 列加函数导致索引失效 |

| 8 | ROLLUP 自动小计，NULL 是聚合行 | 业务 NULL 与聚合 NULL 混淆 |

| 9 | GROUP_CONCAT 默认 1024 字节截断 | 线上拼字符串被截断不排查 |

| 10 | DISTINCT 与 GROUP BY 去重等价 | DISTINCT 想作用部分列 |

| 11 | 聚合函数不能嵌套 | SUM(AVG(x)) 直接写报错 |

| 12 | 多列 GROUP BY 按组合分组 | 顺序影响索引利用 |

| 13 | MySQL 允许 HAVING 用别名 | 跨库移植不兼容 |

| 14 | BIT_OR 合并权限位 | 空组返回值记错 |

| 15 | SUM(CASE WHEN) 条件聚合 | 行转列写不对 |

| 16 | 提前 WHERE + 联合索引优化分组 | 临时表落盘不监控 |

| 17 | MySQL 不支持 GROUPING SETS | 多维分析硬写 UNION ALL |

| 18 | STDDEV/VAR 统计波动 | 业务误用总体/样本口径 |

| 19 | 无 GROUP BY 聚合总返回一行 | 有 GROUP BY 无数据返回 0 行 |

| 20 | NULL、ROLLUP、ONLY_FULL_GROUP_BY 三大坑 | 8.0 隐式排序变化 |

练习题

1. 题目：orders(order_id, user_id, amount, status) 中统计每个用户的订单数和已支付订单数。

参考答案：

   SELECT user_id,
          COUNT(*) AS total_cnt,
          SUM(CASE WHEN status='PAID' THEN 1 ELSE 0 END) AS paid_cnt
   FROM orders
   GROUP BY user_id;

2. 题目：为什么 SELECT dept, name, COUNT(*) FROM emp GROUP BY dept 在 MySQL 8.0 报错？怎么改？

参考答案：因为 ONLY_FULL_GROUP_BY 要求非聚合列 name 必须在 GROUP BY 中；可改为 GROUP BY dept, name 或 ANY_VALUE(name)。

3. 题目：score 列值为 (10, 20, NULL)，AVG(score) 结果是多少？为什么？

参考答案：15，因为 AVG 忽略 NULL，分母是 2 不是 3。

4. 题目：GROUP_CONCAT 拼出来的字符串比预期短，可能原因？

参考答案：group_concat_max_len 默认 1024 字节，超长静默截断；SET SESSION group_concat_max_len=102400 调大。

5. 题目：要在一次查询中同时返回按 dept 分组和按 dept+job 分组的人数，MySQL 怎么写？

参考答案：用 UNION ALL 拼两个 GROUP BY，或用 WITH ROLLUP（若层级固定）。

下一篇预告：第 13 章 EXPLAIN执行计划面试精讲

— 全文完 —回到顶部 ↑

下载推广海报

DISCUSSION

文章回复

0 条公开回复

还没有回复，欢迎参与讨论。

第 12 章 聚合与分组面试精讲

目录

题目 1：WHERE 与 HAVING 的区别，能否互换

考点

参考答案

追问

题目 2：COUNT(*)、COUNT(1)、COUNT(列) 三者区别与性能

考点

参考答案

追问

题目 3：ONLY_FULL_GROUP_BY 是什么，为什么 5.7 默认开启

考点

参考答案

追问

题目 4：GROUP BY 后 SELECT 非聚合列为什么报错

考点

参考答案

追问

题目 5：SUM、AVG、MAX、MIN 对 NULL 的处理

考点

参考答案

追问

题目 6：COUNT(*) 遇到 NULL 行会怎么算

考点

参考答案

追问

题目 7：GROUP BY 能否使用索引，什么情况下会 filesort

考点

参考答案

追问

题目 8：WITH ROLLUP 的作用，MySQL 8.0 有什么增强

考点

参考答案

追问

题目 9：GROUP_CONCAT 的作用、长度限制与坑

考点

参考答案

追问

题目 10：DISTINCT 与 GROUP BY 去重性能谁好

考点

参考答案

追问

题目 11：聚合函数能否嵌套，比如 SUM(AVG(x))

考点

参考答案

追问

题目 12：GROUP BY 多个列的语义是什么

考点

参考答案

追问

题目 13：HAVING 中能否使用 SELECT 别名

考点

参考答案

追问

题目 14：BIT_AND、BIT_OR、BIT_XOR 聚合函数的用途

考点

参考答案

追问

题目 15：如何在分组后筛选"组内满足条件的行数"

考点

参考答案

追问

题目 16：分组查询慢，有哪些优化手段

考点

参考答案

追问

题目 17：MySQL 8.0 支持 GROUPING SETS 吗，怎么替代

考点

参考答案

追问

题目 18：STDDEV、VARIANCE 等统计聚合函数什么时候用

考点

参考答案

追问

题目 19：聚合查询返回空结果时 COUNT 的行为

考点

参考答案

追问

题目 20：常见坑与注意事项

考点

第 12 章聚合与分组面试精讲