第 6 章聚合函数与GROUP BY

站长2026-10-22 14:00:00

内容摘要

前面的查询都是"一行对一行"，本篇进入"多行压缩成一行"的世界——聚合。`COUNT`/`SUM`/`AVG`/`MAX`/`MIN` 是五个最常用的聚合函数，配合 `GROUP BY` 能"按城市统计平均成绩"，配合 `HAVING` 能"过滤分组"。我们还会讲 `GROUP_CONCAT` 把一组行拼成字符串，以及聚合中 NULL 的特殊行为。这是写报表 SQL 的核心。本示例基于 MySQL 8.0，所有 SQL 均按该版本语法书写。

📖 本篇简介：前面的查询都是"一行对一行"，本篇进入"多行压缩成一行"的世界——聚合。COUNT/SUM/AVG/MAX/MIN 是五个最常用的聚合函数，配合 GROUP BY 能"按城市统计平均成绩"，配合 HAVING 能"过滤分组"。我们还会讲 GROUP_CONCAT 把一组行拼成字符串，以及聚合中 NULL 的特殊行为。这是写报表 SQL 的核心。本示例基于 MySQL 8.0，所有 SQL 均按该版本语法书写。

1 聚合函数概述

聚合函数（aggregate function）把多行数据"压缩"成一行，常用于报表统计。MySQL 8.0 内置的聚合函数主要有：

| 函数 | 作用 | 忽略 NULL？ |

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

| COUNT() | 计数 | 取决于参数 |

| SUM() | 求和 | 忽略 |

| AVG() | 平均值 | 忽略 |

| MAX() | 最大值 | 忽略 |

| MIN() | 最小值 | 忽略 |

| GROUP_CONCAT() | 拼接字符串 | 忽略 |

| BIT_AND/OR/XOR | 位运算 | 忽略 |

核心心智模型：聚合函数把"行集合"作为输入，输出"一个标量值"。如果不配合 GROUP BY，整个表就是一个大组，输出一行结果。

2 COUNT：计数

2.1 三种 COUNT 的区别

这是面试高频题：

-- 写法 A：COUNT(*) 数所有行，含 NULL 和重复
SELECT COUNT(*) FROM students;

-- 写法 B：COUNT(列名) 数该列非 NULL 的行
SELECT COUNT(gender) FROM students;

-- 写法 C：COUNT(常量) 等价于 COUNT(*)
SELECT COUNT(1) FROM students;

| 写法 | 含义 | 是否数 NULL 行 |

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

| COUNT(*) | 总行数 | 是 |

| COUNT(1) | 等价 COUNT(*) | 是 |

| COUNT(列) | 该列非 NULL 行数 | 否 |

| COUNT(DISTINCT 列) | 该列去重后的非 NULL 值数 | 否 |

-- 统计 students 表一共有多少人
SELECT COUNT(*) AS total FROM students;

-- 统计性别已经填了的学生有多少人
SELECT COUNT(gender) AS gender_filled FROM students;

-- 统计有多少个不同的城市
SELECT COUNT(DISTINCT city) AS city_count FROM students;

2.2 COUNT(*) 的性能

InnoDB 中 COUNT(*) 在没有 WHERE 时需要扫一遍索引，比 MyISAM 慢（MyISAM 内部维护了行数计数）。但 MySQL 8.0 对 COUNT(*) 做了优化——会选最小的二级索引扫描，不一定扫主键。

生产环境"select count(*) from 大表"还是慢的，常见替代方案：

用 EXPLAIN 估算行数（不够精确但快）；
单独建一张元数据表，每次增删时更新计数；
业务侧用 Redis 缓存。

3 SUM / AVG / MAX / MIN

3.1 基础用法

-- 全表统计：总分、平均分、最高分、最低分
SELECT
    SUM(score)  AS total_score,
    AVG(score)  AS avg_score,
    MAX(score)  AS max_score,
    MIN(score)  AS min_score
FROM students;

注意：

SUM/AVG 只对数值列有意义，对字符串会转 0；
MAX/MIN 对日期、字符串也能工作（按排序规则）；
所有聚合函数都会忽略 NULL——如果一列全是 NULL，SUM 返回 NULL 而不是 0。

3.2 AVG 与 NULL 的陷阱

-- 如果 100 个学生里有 10 个 score 是 NULL
-- AVG(score) = 总分 / 90（只除以非 NULL 的行数）
SELECT AVG(score) FROM students;

这通常不是你想要的——业务上"平均成绩"一般希望把 NULL 当 0 算：

-- 把 NULL 当 0 再求平均
SELECT AVG(IFNULL(score, 0)) FROM students;

3.3 配 WHERE 先过滤再聚合

-- 只统计北京学生的成绩
SELECT
    AVG(score) AS avg_score_bj,
    MAX(score) AS max_score_bj
FROM students
WHERE city = '北京';

执行顺序：FROM → WHERE → 聚合。WHERE 先把北京学生挑出来，聚合函数再作用于这些行。

4 GROUP BY 分组

4.1 基础语法

GROUP BY 列 把相同列值的行归为一组，每组输出一行聚合结果：

-- 按城市分组，统计每个城市的学生人数和平均成绩
SELECT
    city,
    COUNT(*)    AS student_count,
    AVG(score)  AS avg_score
FROM students
GROUP BY city;

输出大概是：

| city | student_count | avg_score |

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

| 北京 | 50 | 82.3 |

| 上海 | 30 | 88.1 |

| 广州 | 20 | 79.5 |

4.2 SELECT 列的规则

核心规则：GROUP BY 后，SELECT 列表里只能出现两类列：

1. 出现在 GROUP BY 后面的列；

2. 被聚合函数包起来的列。

-- 错误：name 既不在 GROUP BY 里，也没被聚合
SELECT city, name, AVG(score)
FROM students
GROUP BY city;
-- MySQL 8.0 默认开启 ONLY_FULL_GROUP_BY，直接报错

MySQL 5.7 之前这种写法能跑（随机取一行的 name），8.0 默认严格模式报错。这是好事——避免结果不可预测。

4.3 多列分组

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

-- 按城市 + 性别分组，统计每组人数
SELECT
    city, gender, COUNT(*) AS cnt
FROM students
GROUP BY city, gender;

输出会有 (北京, M)、(北京, F)、(上海, M) 这样的组合。

4.4 GROUP BY 与 ORDER BY

分组后通常要按聚合结果排序：

-- 按城市分组，按人数从多到少排
SELECT city, COUNT(*) AS cnt
FROM students
GROUP BY city
ORDER BY cnt DESC;

注意 ORDER BY 可以用 SELECT 里的别名。

5 HAVING 分组后过滤

5.1 WHERE 与 HAVING 的区别

WHERE：在分组前过滤行，不能用聚合函数；
HAVING：在分组后过滤组，可以用聚合函数。

-- 错误：WHERE 里不能用聚合函数
SELECT city, AVG(score) AS avg
FROM students
WHERE AVG(score) > 80    -- 报错
GROUP BY city;

-- 正确：用 HAVING
SELECT city, AVG(score) AS avg
FROM students
GROUP BY city
HAVING AVG(score) > 80;

5.2 同时用 WHERE 和 HAVING

-- 先 WHERE 过滤掉成绩不及格的学生，再分组，再 HAVING 过滤分组
SELECT city, AVG(score) AS avg, COUNT(*) AS cnt
FROM students
WHERE score >= 60          -- 行级过滤：只算及格的
GROUP BY city
HAVING COUNT(*) >= 10;     -- 组级过滤：该城市至少 10 人

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

5.3 HAVING 用别名

MySQL 允许 HAVING 引用 SELECT 别名：

SELECT city, AVG(score) AS avg_score
FROM students
GROUP BY city
HAVING avg_score > 80;    -- 这里能用别名

6 GROUP_CONCAT 行拼接

6.1 基本用法

GROUP_CONCAT 把一组行的某列拼成一个字符串，用逗号分隔：

-- 按城市分组，把每个城市的学生姓名拼在一起
SELECT
    city,
    GROUP_CONCAT(name) AS students
FROM students
GROUP BY city;

输出：

| city | students |

|---|---|

| 北京 | 张三,李四,王五 |

| 上海 | 赵六,钱七 |

6.2 自定义分隔符与排序

-- 自定义分隔符 + 排序 + 去重
SELECT
    city,
    GROUP_CONCAT(name ORDER BY score DESC SEPARATOR ' | ') AS top_students
FROM students
GROUP BY city;

6.3 长度限制

GROUP_CONCAT 结果受 group_concat_max_len 变量限制，默认 1024 字节。超长会被截断：

-- 临时调大（会话级）
SET SESSION group_concat_max_len = 102400;

业务上如果你发现拼接结果少了尾巴，先检查这个参数。

7 WITH ROLLUP 与分组汇总

WITH ROLLUP 在 GROUP BY 结果上额外加一行"总计"：

-- 按城市分组，最后加一行所有城市的总和
SELECT
    city,
    COUNT(*) AS cnt,
    AVG(score) AS avg
FROM students
GROUP BY city WITH ROLLUP;

最后一行的 city 会是 NULL（表示"总计"），cnt/avg 是全表的聚合值。

8.0 还支持 GROUP BY ... WITH ROLLUP 配合 GROUPING() 函数区分"这是分组行还是总计行"。

8 聚合函数速查表

| 需求 | 写法 | 备注 |

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

| 总行数 | COUNT(*) | 含 NULL |

| 某列非空行数 | COUNT(列) | 忽略 NULL |

| 去重值数 | COUNT(DISTINCT 列) | 统计不同值个数 |

| 求和 | SUM(列) | 忽略 NULL |

| 平均 | AVG(列) | 忽略 NULL，注意 NULL 当 0 |

| 最大 | MAX(列) | 支持日期/字符串 |

| 最小 | MIN(列) | 支持日期/字符串 |

| 行拼接 | GROUP_CONCAT(列) | 受 max_len 限制 |

| 按列分组 | GROUP BY 列 | SELECT 列规则严格 |

| 组过滤 | HAVING 聚合(列) > x | 不能用 WHERE |

| 总计行 | GROUP BY ... WITH ROLLUP | 8.0 增强 |

9 常见坑与注意事项

坑 1：`COUNT(列)` 与 `COUNT(*)` 混用。

数总行数用 COUNT(*)；数某列非空行数才用 COUNT(列)。业务统计写错会少算。

坑 2：AVG 自动把 NULL 当不存在。

业务上需要"全员平均"时，用 AVG(IFNULL(列, 0))。

坑 3：SELECT 列不在 GROUP BY 也不被聚合。

8.0 严格模式直接报错；5.7 旧代码升级时要批量排查。

坑 4：WHERE 里用聚合函数。

必须用 HAVING。

坑 5：GROUP BY 后忘了 ORDER BY，结果顺序不稳定。

分组输出顺序不保证，业务需要排序就显式 ORDER BY。

坑 6：GROUP_CONCAT 结果被截断。

默认 1024 字节，拼接大字段前先调 group_concat_max_len。

坑 7：大表 `COUNT(*)` 拖库。

千万级表 COUNT(*) 没有 WHERE 时很慢，业务侧用估算或缓存。

坑 8：DISTINCT 与 GROUP BY 混用。

SELECT DISTINCT a, COUNT(*) FROM t GROUP BY a 是冗余的，因为 GROUP BY 已经天然去重。

10 练习题

练习 1：基础统计

题目：统计 students 表的总人数、平均分、最高分、最低分。

SELECT
    COUNT(*)   AS total,
    AVG(score) AS avg_score,
    MAX(score) AS max_score,
    MIN(score) AS min_score
FROM students;

</details>

练习 2：按城市分组

题目：统计每个城市的学生人数和平均成绩，按人数从多到少排。

SELECT
    city,
    COUNT(*)   AS cnt,
    AVG(score) AS avg_score
FROM students
GROUP BY city
ORDER BY cnt DESC;

</details>

练习 3：HAVING 过滤

题目：找出平均成绩大于 80 分的城市，并显示其平均分和人数。

SELECT
    city,
    AVG(score) AS avg_score,
    COUNT(*)   AS cnt
FROM students
GROUP BY city
HAVING avg_score > 80;

</details>

练习 4：多列分组

题目：按城市 + 性别分组，统计每组人数，只要人数 >= 5 的组。

SELECT
    city, gender, COUNT(*) AS cnt
FROM students
GROUP BY city, gender
HAVING cnt >= 5;

</details>

练习 5：拼接姓名

题目：按城市分组，把每个城市学生的姓名按成绩从高到低拼在一起，用 | 分隔。

SELECT
    city,
    GROUP_CONCAT(name ORDER BY score DESC SEPARATOR ' | ') AS students
FROM students
GROUP BY city;

</details>

练习 6：NULL 平均

题目：业务要求"平均成绩把缺考（score 为 NULL）的学生当 0 分算"，应该怎么写？

SELECT city, AVG(IFNULL(score, 0)) AS avg_score
FROM students
GROUP BY city;

</details>

下一篇预告：第 7 章多表连接JOIN

— 全文完 —回到顶部 ↑

下载推广海报

DISCUSSION

文章回复

0 条公开回复

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

第 6 章聚合函数与GROUP BY

目录

1 聚合函数概述

2 COUNT：计数

2.1 三种 COUNT 的区别

2.2 COUNT(*) 的性能

3 SUM / AVG / MAX / MIN

3.1 基础用法

3.2 AVG 与 NULL 的陷阱

3.3 配 WHERE 先过滤再聚合

4 GROUP BY 分组

4.1 基础语法

4.2 SELECT 列的规则

4.3 多列分组

4.4 GROUP BY 与 ORDER BY

5 HAVING 分组后过滤

5.1 WHERE 与 HAVING 的区别

5.2 同时用 WHERE 和 HAVING

5.3 HAVING 用别名

6 GROUP_CONCAT 行拼接

6.1 基本用法

6.2 自定义分隔符与排序

6.3 长度限制

7 WITH ROLLUP 与分组汇总

8 聚合函数速查表

9 常见坑与注意事项

10 练习题

练习 1：基础统计

练习 2：按城市分组

练习 3：HAVING 过滤

练习 4：多列分组

练习 5：拼接姓名

练习 6：NULL 平均

文章推广海报

文章回复

第 6 章 聚合函数与GROUP BY

目录

1 聚合函数概述

2 COUNT：计数

2.1 三种 COUNT 的区别

2.2 COUNT(*) 的性能

3 SUM / AVG / MAX / MIN

3.1 基础用法

3.2 AVG 与 NULL 的陷阱

3.3 配 WHERE 先过滤再聚合

4 GROUP BY 分组

4.1 基础语法

4.2 SELECT 列的规则

4.3 多列分组

4.4 GROUP BY 与 ORDER BY

5 HAVING 分组后过滤

5.1 WHERE 与 HAVING 的区别

5.2 同时用 WHERE 和 HAVING

5.3 HAVING 用别名

6 GROUP_CONCAT 行拼接

6.1 基本用法

6.2 自定义分隔符与排序

6.3 长度限制

7 WITH ROLLUP 与分组汇总

8 聚合函数速查表

9 常见坑与注意事项

10 练习题

练习 1：基础统计

练习 2：按城市分组

练习 3：HAVING 过滤

练习 4：多列分组

练习 5：拼接姓名

练习 6：NULL 平均

第 6 章聚合函数与GROUP BY