聚合函数
导学
聚合函数是 SQL 的"统计利器"——它们接收多行输入，输出单一汇总值。求总人数、平均分、最高价格……这些需求都依赖聚合函数。
定义
聚合函数（Aggregate Functions）：对一组值执行计算并返回单一值的函数。MySQL 5.7 核心聚合函数包括 COUNT、SUM、AVG、MAX、MIN、GROUP_CONCAT。
核心聚合函数
| 函数 | 作用 | NULL 处理 |
|---|---|---|
COUNT(*) | 统计行数 | 不忽略，统计所有行 |
COUNT(列) | 统计该列非 NULL 的行数 | 忽略 NULL |
SUM(列) | 求和 | 忽略 NULL |
AVG(列) | 平均值 | 忽略 NULL |
MAX(列) | 最大值 | 忽略 NULL |
MIN(列) | 最小值 | 忽略 NULL |
GROUP_CONCAT(列) | 将多行值拼接为字符串 | 忽略 NULL |
演示数据准备
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20),
dept VARCHAR(20),
score DECIMAL(5,2)
);
INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);
当前 employees 表中的完整数据如下：
| emp_id | emp_name | dept | score |
|---|---|---|---|
| 1 | 大翔 | 技术部 | 100 |
| 2 | 白歌 | 技术部 | NULL |
SQL 示例
场景一：综合统计（COUNT / SUM / AVG / MAX / MIN）
当前数据状态：见上文 employees 表完整数据。
执行语句：
SELECT
COUNT(*) AS 员工总数,
COUNT(score) AS 有绩效分人数,
COUNT(DISTINCT dept) AS 部门种类数,
AVG(score) AS 平均分,
MAX(score) AS 最高分,
MIN(score) AS 最低分,
SUM(score) AS 总分
FROM employees;
操作后结果：
| 员工总数 | 有绩效分人数 | 部门种类数 | 平均分 | 最高分 | 最低分 | 总分 |
|---|---|---|---|---|---|---|
| 2 | 1 | 1 | 100.0000 | 100.00 | 100.00 | 100.00 |
结果解读：
COUNT(*)= 2：统计所有行COUNT(score)= 1：只统计score非 NULL 的行（白歌为 NULL）COUNT(DISTINCT dept)= 1：去重后计数（技术部）AVG(score)= 100.0000：100/1 = 100.0000，忽略 NULL（白歌）MAX(score)= 100.00：最高分是大翔MIN(score)= 100.00：最低分是大翔（唯一非 NULL 值）SUM(score)= 100.00：所有非 NULL 绩效分之和
场景二：字符串拼接（GROUP_CONCAT）
当前数据状态：见上文 employees 表完整数据。
执行语句：
SELECT
dept,
GROUP_CONCAT(emp_name ORDER BY emp_name ASC SEPARATOR '、') AS 员工名单
FROM employees
GROUP BY dept;
操作后结果：
| dept | 员工名单 |
|---|---|
| 技术部 | 大翔、白歌 |
结果解读：GROUP_CONCAT 将同一组内的多行字符串拼接起来。ORDER BY 控制拼接顺序（按姓名升序），SEPARATOR 指定分隔符为 '、'。默认分隔符是逗号，最大长度受 group_concat_max_len 系统变量限制（默认 1024 字节）。当前表中所有员工都在技术部，分组后只有 1 行。
再看一个带 DISTINCT 的例子：
SELECT
GROUP_CONCAT(DISTINCT dept ORDER BY dept ASC SEPARATOR ' | ') AS 所有部门
FROM employees;
操作后结果：
| 所有部门 |
|---|
| 技术部 |
结果解读：DISTINCT 先去重，再拼接。注意这里不需要 GROUP BY，因为是对全表做聚合。
场景三：聚合函数与 NULL 的陷阱
当前数据状态：见上文 employees 表完整数据（2 行，其中 1 行 score 为 NULL：白歌）。
执行语句：
SELECT
COUNT(*) AS 总行数,
COUNT(score) AS 非NULL行数,
SUM(score) AS 总和,
AVG(score) AS 平均值,
AVG(IFNULL(score, 0)) AS 含NULL平均值,
MAX(score) AS 最大值,
MIN(score) AS 最小值
FROM employees;
操作后结果：
| 总行数 | 非NULL行数 | 总和 | 平均值 | 含NULL平均值 | 最大值 | 最小值 |
|---|---|---|---|---|---|---|
| 2 | 1 | 100 | 100.0000 | 50.0000 | 100 | 100 |
结果解读：
COUNT(*)= 2：统计所有行COUNT(score)= 1：忽略 NULL，只统计非 NULL 行SUM(score)= 100：忽略 NULLAVG(score)= 100.0000：100 / 1 = 100.0000，忽略 NULL 后除以非 NULL 行数AVG(IFNULL(score, 0))= 50.0000：(100+0)/2 = 50.0000，NULL 被替换为 0 后参与计算MAX(score)= 100：忽略 NULL 后的最大值MIN(score)= 100：忽略 NULL 后的最小值（唯一非 NULL 值）
关键区别：
AVG(score)忽略 NULL 后除以非 NULL 行数；若希望 NULL 视为 0，需用AVG(IFNULL(score, 0))。两者的语义完全不同，使用时要根据业务需求选择。
场景四：聚合函数与 WHERE 结合
当前数据状态：见上文 employees 表完整数据。
执行语句：
SELECT
COUNT(*) AS 技术部人数,
AVG(score) AS 技术部平均分,
MAX(score) AS 技术部最高分
FROM employees
WHERE dept = '技术部';
操作后结果：
| 技术部人数 | 技术部平均分 | 技术部最高分 |
|---|---|---|
| 2 | 100.0000 | 100.00 |
结果解读：WHERE 先过滤出技术部的员工（大翔、白歌），然后聚合函数在这 2 行上计算。白歌的 score 为 NULL，因此 AVG 计算为 100/1 = 100.00，除以 1（非 NULL 行数）而非 2。
场景五：同时统计多个维度（CASE WHEN + 聚合）
当前数据状态：见上文 employees 表完整数据。
执行语句：
SELECT
COUNT(*) AS 总人数,
COUNT(CASE WHEN dept = '技术部' THEN 1 END) AS 技术部人数,
COUNT(CASE WHEN dept = '产品部' THEN 1 END) AS 产品部人数,
COUNT(CASE WHEN score >= 90 THEN 1 END) AS 优秀人数,
COUNT(CASE WHEN score IS NULL THEN 1 END) AS 未录入人数
FROM employees;
操作后结果：
| 总人数 | 技术部人数 | 产品部人数 | 优秀人数 | 未录入人数 |
|---|---|---|---|---|
| 2 | 2 | 0 | 1 | 1 |
结果解读：CASE WHEN 配合 COUNT 是单查询实现条件统计的经典技巧。不满足条件的行返回 NULL，COUNT 忽略 NULL，因此只统计满足条件的行。
- 总人数：2
- 技术部人数：大翔、白歌 = 2
- 产品部人数：0（没有产品部员工）
- 优秀人数（score >= 90）：大翔 100 = 1（白歌 NULL 不满足）
- 未录入人数（score IS NULL）：白歌 = 1
场景六：空表和全 NULL 的情况
当前数据状态：创建一张空表和一张全 NULL 表进行演示。
CREATE TABLE IF NOT EXISTS empty_table (
id INT,
val INT
);
CREATE TABLE IF NOT EXISTS all_null (
id INT,
val INT
);
INSERT INTO all_null (id, val) VALUES (1, NULL), (2, NULL);
执行语句（空表）：
SELECT
COUNT(*) AS cnt,
COUNT(val) AS cnt_val,
SUM(val) AS sum_val,
AVG(val) AS avg_val,
MAX(val) AS max_val,
MIN(val) AS min_val
FROM empty_table;
操作后结果：
| cnt | cnt_val | sum_val | avg_val | max_val | min_val |
|---|---|---|---|---|---|
| 0 | 0 | NULL | NULL | NULL | NULL |
结果解读：空表没有任何行。COUNT(*) 返回 0，但 SUM、AVG、MAX、MIN 没有任何输入值，因此都返回 NULL。
执行语句（全 NULL 表）：
SELECT
COUNT(*) AS cnt,
COUNT(val) AS cnt_val,
SUM(val) AS sum_val,
AVG(val) AS avg_val,
MAX(val) AS max_val,
MIN(val) AS min_val
FROM all_null;
操作后结果：
| cnt | cnt_val | sum_val | avg_val | max_val | min_val |
|---|---|---|---|---|---|
| 2 | 0 | NULL | NULL | NULL | NULL |
结果解读：all_null 表有 2 行，但 val 全部为 NULL。COUNT(*) = 2（统计行），COUNT(val) = 0（忽略 NULL）。所有其他聚合函数都返回 NULL，因为没有任何有效的输入值。
常见误区
| 误区 | 正解 |
|---|---|
COUNT(列) 和 COUNT(*) 一样 | COUNT(*) 统计所有行；COUNT(列) 忽略 NULL。 |
| 聚合函数把 NULL 当 0 | 聚合函数忽略 NULL，不是当 0。 |
SUM 可以对字符串使用 | MySQL 会尝试将字符串隐式转为数字，非数字串转为 0，结果不可预期。 |
MAX/MIN 只能用于数字 | 也可以用于字符串（按字母顺序）和日期。 |
| 聚合函数不能和普通列混用 | 不带 GROUP BY 时，聚合函数会对全表汇总为一行，普通列会随机取一行值（MySQL 5.7 默认允许，但结果无意义）。 |
面试考点
Q：COUNT(*)、COUNT(1)、COUNT(列) 的区别？
COUNT(*)和COUNT(1)在 MySQL 5.7 中优化器会同等对待，都统计所有行；COUNT(列)统计该列非 NULL 的行数。
Q：聚合函数的结果可以是 NULL 吗？
可以。当没有任何输入行（如空表），或所有值都是 NULL 时，
SUM、AVG、MAX、MIN、GROUP_CONCAT都返回 NULL。COUNT返回 0。
Q：如何计算"包含 NULL 的平均值"（NULL 视为 0）？
AVG(IFNULL(score, 0))或AVG(COALESCE(score, 0))。注意这与默认AVG(score)的语义完全不同。
Q：GROUP_CONCAT 有长度限制吗？
有。默认最大长度由
group_concat_max_len系统变量控制（默认 1024 字节）。超出限制会被截断，可以通过SET group_concat_max_len = 100000;调整。
小结
- 聚合函数将多行汇总为单一值
COUNT(*)统计行数，COUNT(列)统计非 NULL 行数- 所有聚合函数（除
COUNT(*)）都忽略 NULL GROUP_CONCAT是多行转字符串的利器- 聚合函数在空表或全 NULL 情况下可能返回 NULL（
COUNT返回 0） CASE WHEN+ 聚合函数可以实现单查询多维度统计
下一章引子：聚合函数配合 GROUP BY，就能实现"分组统计"——这是数据分析中最常用的 SQL 模式之一。