五个聚合函数（PPT 第 59 页）#

五个函数在本机 emp 表（30 条数据）上跑出来的真实结果：

1
select count(*) as c_star, count(id) as c_id, count(1) as c_1, count(job) as c_job, count(salary) as c_salary from emp;
2
select avg(salary) as avg_s, round(avg(salary),2) as avg_r2, min(salary) as min_s, max(salary) as max_s, sum(salary) as sum_s from emp;

1
+--------+------+-----+-------+----------+
2
| c_star | c_id | c_1 | c_job | c_salary |
3
+--------+------+-----+-------+----------+
4
|     30 |   30 |  30 |    29 |       29 |
5
+--------+------+-----+-------+----------+
6

7
+-----------+---------+-------+-------+--------+
8
| avg_s     | avg_r2  | min_s | max_s | sum_s  |
9
+-----------+---------+-------+-------+--------+
10
| 7548.2759 | 7548.28 |  4700 | 15000 | 218900 |
11
+-----------+---------+-------+-------+--------+

注意一：null 不参与所有聚合函数的运算（PPT 第 59 页）#

PPT 第 59 页的第一条注意是原文：

null 值不参与所有聚合函数的运算。

这句话看着平淡，实测一下就知道它有多容易算错：

平均薪资这个数最好验算一遍：218900 ÷ 29 = 7548.27586……，四舍五入就是 7548.2759；如果用 30 当分母（错误地以为”没薪资的人按 0 算”），会得到 7296.6667——和正确答案差了 250 多。

注意二：统计数量的三种写法，推荐 count(*)（PPT 第 59 页）#

PPT 第 59 页的第二条注意是原文：

统计数量可以使用：count(*) count(字段) count(常量)，推荐使用 count(*)。

三种写法实测出来的区别：

推荐 count(*) 的理由有两层：一是它语义最直白（“数行数”就该这么写）；二是它是 SQL 的标准写法，数据库自己知道怎么数最省事，不需要真的去读某一列的值。而 count(字段) 只有在一种情况下才是你想要的——你确实想数”这一列填了值的有多少个”（比如”有多少员工分配了职位”）。

语法（PPT 第 60 页）#

1
select 字段列表 from 表名 [where 条件列表] group by 分组字段名 [having 分组后过滤条件];

中括号里的两段都是可选的：where 是”分组之前先筛掉一些行”（46 篇学过），having 是”分组之后再筛掉一些组”（下面马上讲）。

实测一：按职位分组统计人数#

1
select job, count(*) from emp group by job;

1
+------+----------+
2
| job  | count(*) |
3
+------+----------+
4
|    4 |        1 |
5
|    2 |       12 |
6
|    3 |        1 |
7
|    1 |        6 |
8
|    5 |        9 |
9
| NULL |        1 |
10
+------+----------+

这条结果对应的是”每个职位有多少人”（1 班主任 6 人、2 讲师 12 人、3 学工主管 1 人、4 教研主管 1 人、5 咨询师 9 人、没填职位 1 人）。

实测二：按性别分组看人数与平均薪资#

select 里的字段列表可以同时放分组字段和聚合函数，一条 SQL 就能省掉两趟查询：

1
select gender, count(*) from emp group by gender;

1
+--------+----------+
2
| gender | count(*) |
3
+--------+----------+
4
|      1 |       27 |
5
|      2 |        3 |
6
+--------+----------+

1
select gender, avg(salary) from emp group by gender;

1
+--------+-----------+
2
| gender | avg(salary) |
3
+--------+-----------+
4
|      1 | 7346.1538 |
5
|      2 | 9300.0000 |
6
+--------+-----------+

注意：分组之后查其他字段没有意义（PPT 第 60 页）#

PPT 第 60 页的第三条注意是原文：

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

想要验证这句”无任何意义”，本机实测直接报错：

1
select job, name, count(*) from emp group by job;

1
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db01.emp.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

实测：where 里用聚合函数会报错#

1
select job, count(*) from emp where count(*) > 3 group by job;

1
ERROR 1111 (HY000): Invalid use of group function

1
select job, count(*) as cnt from emp group by job having count(*) > 3;

1
+------+-----+
2
| job  | cnt |
3
+------+-----+
4
|    2 |  12 |
5
|    1 |   6 |
6
|    5 |   9 |
7
+------+-----+

组合实战：先筛、再分组、再过滤#

真实需求往往三段都要：先把一部分数据筛掉（where）→ 再分组统计（group by + 聚合）→ 最后把不满足条件的组去掉（having）。

需求：先只看 2000-01-01 之后入职的员工，按职位分组，找出人数大于 2 的职位：

1
select job, count(*) as cnt from emp where entry_date > '2000-01-01' group by job having count(*) > 2;

1
+------+-----+
2
| job  | cnt |
3
+------+-----+
4
|    2 |  12 |
5
|    1 |    6 |
6
|    5 |    9 |
7
+------+-----+

课程配套练习里还有一道同型题——先查入职时间在 2015-01-01（含）以前的员工，按职位分组，获取员工数量大于等于 2 的职位：

1
select job, count(*) from emp where entry_date <= '2015-01-01' group by job having count(*) >= 2;

1
+------+----------+
2
| job  | count(*) |
3
+------+----------+
4
|    2 |       11 |
5
|    1 |        5 |
6
|    5 |        7 |
7
+------+----------+

必答问答（PPT 第 61 页）#

一、知识回顾（读完直接做下面的实践题）#

1. 聚合函数的定义是”将一列数据作为一个整体，进行纵向计算”；五个函数：count 统计数量、max 最大值、min 最小值、avg 平均值、sum 求和
2. null 不参与所有聚合函数的运算：它会跳过 count(字段)、不进入 sum/avg 的分子分母、不参与 min/max 的比较
3. 统计数量的三种写法：count(*)（数总行数，推荐）、count(字段)（只数该字段非空的值）、count(常量)（等价于数总行数）
4. 分组查询语法：select 字段列表 from 表名 [where 条件列表] group by 分组字段名 [having 分组后过滤条件];——select 里能写的是分组字段 + 聚合函数
5. 分组时 NULL 自己成一组：group by job 的结果里有单独一行 job 为 NULL（李云），而 count(job) 却数不到他——两个现象一个原因（NULL 不参与聚合，但分组时算一类值）
6. where 与 having 的两条区别：① 执行时机不同（where 分组前过滤，不满足条件的行不参与分组；having 分组后过滤）；② 判断条件不同（where 不能用聚合函数，having 可以）
7. 执行顺序：where > 聚合函数 > having（再加上外层就是 from → where → group by → 聚合 → having → select → order by → limit）
8. 实测数字（本机 MySQL 9.0.1，emp 表 30 条）：count(*) = 30、count(job) = 29、avg(salary) = 7548.2759（保留两位 7548.28，分母是 29）、min = 4700、max = 15000、sum = 218900
9. 实测分组结果：按职位 group by job → 1 有 6 人、2 有 12 人、3 有 1 人、4 有 1 人、5 有 9 人、NULL 有 1 人；按性别 group by gender → 1 有 27 人（平均 7346.1538）、2 有 3 人（平均 9300.0000）
10. 两条报错原文：where count(*) > 3 → ERROR 1111 (HY000): Invalid use of group function；group by job 后 select 非分组字段 name → ERROR 1055 (42000)（提示与 sql_mode=only_full_group_by 不兼容）

二、裸写题#

• 2-1 先把五个聚合函数各跑一遍 统计该公司：① 员工数量；② 平均薪资；③ 最低薪资；④ 最高薪资；⑤ 薪资总额。 （练习文件 test_47_聚合函数.sql 里给了题目注释和写作区。）

  提示（先自己想，实在想不出再点开）

  一级 · 思路：这五问都是”把一整列当成一个整体算一个数”，用聚合函数；注意它们不需要 group by（整张表算一个值） 二级 · 方法：select 聚合函数(字段) from 表名;——统计数量 count、平均 avg、最小 min、最大 max、求和 sum；统计数量推荐 count(*) 三级 · 骨架：select count(*) from emp; / select ____(salary) from emp;（平均值）/ select min(salary), ____(salary) from emp; / select ____(salary) from emp;（求和）

  参考答案（做完再点开）

  1
  select count(*) as 员工数量 from emp;
  2
  select avg(salary) as 平均薪资 from emp;
  3
  select min(salary) as 最低薪资 from emp;
  4
  select max(salary) as 最高薪资 from emp;
  5
  select sum(salary) as 薪资总额 from emp;
  6
  -- 一条 SQL 里全查出来也可以
  7
  select count(*) as 员工数量, avg(salary) as 平均薪资, min(salary) as 最低薪资, max(salary) as 最高薪资, sum(salary) as 薪资总额 from emp;

  本机实测结果（MySQL 9.0.1，db01.emp）：

  1
  +--------+-----------+-----------+-----------+-----------+
  2
  | 员工数量 | 平均薪资  | 最低薪资  | 最高薪资  | 薪资总额  |
  3
  +--------+-----------+-----------+-----------+-----------+
  4
  |     30 | 7548.2759 |      4700 |     15000 |    218900 |
  5
  +--------+-----------+-----------+-----------+-----------+

  平均薪资这一格一定要会验算：218900 ÷ 29 = 7548.2759——分母是 29 不是 30，因为李云那条数据的 salary 是 NULL，NULL 不参与聚合运算。想保留两位小数可以套一个四舍五入函数 round(avg(salary), 2)，结果是 7548.28。

• 2-2 按性别分组，看人数和平均薪资 把员工按性别分成两组，分别统计每组的人数和平均薪资。

  提示（先自己想，实在想不出再点开）

  一级 · 思路：“按性别分成两组”是这件事的分组依据，“每组各算一个数”是聚合——同一个 select 里，分组字段和聚合函数可以并排放 二级 · 方法：select 分组字段, 聚合函数 from 表名 group by 分组字段;；人数用 count(*)，平均薪资用 avg(salary) 三级 · 骨架：select gender, count(*), ____(salary) from emp ____ ____ gender;

  参考答案（做完再点开）

  1
  select gender, count(*) from emp group by gender;
  2
  select gender, avg(salary) from emp group by gender;
  3
  -- 合并成一条
  4
  select gender, count(*) as 人数, avg(salary) as 平均薪资 from emp group by gender;

  本机实测：

  1
  +--------+-----+-----------+
  2
  | gender | cnt | avg_s     |
  3
  +--------+-----+-----------+
  4
  |      1 |  27 | 7346.1538 |
  5
  |      2 |   3 | 9300.0000 |
  6
  +--------+-----+-----------+

  两组人数 27 + 3 = 30，全员到齐；但男性组的平均薪资分母是 26（27 个男性里李云的薪资为 NULL）——NULL 不参与聚合，在分组里是每组各算各的。数据里 1 表示男、2 表示女（emp 表的 gender 是 tinyint，注释写的是”1:男, 2:女”），读结果时不要看反。

• 2-3 先筛、再分组、再过滤 查出入职时间在 2015-01-01（含当天）以前的员工，按职位分组统计人数，最后只留下人数大于等于 2 的职位。

  提示（先自己想，实在想不出再点开）

  一级 · 思路：一句话里藏着三段——“入职时间在……以前”是筛行（分组之前的事），“按职位分组统计人数”是分组 + 聚合，“人数大于等于 2”是筛组（分组之后的事） 二级 · 方法：筛选行的条件写在 where（这里放入职时间，含当天用 <=）；分组用 group by 分组字段；筛组的条件写在 having（这里放 count(*) >= 2）——having 后面才能用聚合函数，where 里用会报 1111 三级 · 骨架：select job, count(*) from emp where entry_date ____ '2015-01-01' group by ____ having ____ >= 2;

  参考答案（做完再点开）

  1
  select job, count(*) from emp where entry_date <= '2015-01-01' group by job having count(*) >= 2;

  本机实测：

  1
  +------+----------+
  2
  | job  | count(*) |
  3
  +------+----------+
  4
  |    2 |       11 |
  5
  |    1 |        5 |
  6
  |    5 |        7 |
  7
  +------+----------+

  三段各管一段、顺序不能颠倒：where 先砍掉 2015 年之后入职的人（他们连分组都不参与），group by 把剩下的人按职位分成几堆，having 再把人数不够的组整组去掉。所以结果里的数字（11、5、7）比”全表分组”时的 12、6、9 小——差的就是被 where 先筛掉的那些人。

  对照一下：如果把 where 和 having 的位置写反（拿 count(*) >= 2 去 where 里比较），会直接报 ERROR 1111 (HY000): Invalid use of group function——这就是”执行顺序”这条规则在实际写 SQL 时最常撞上的一面墙。

• 2-4 排错：这两条 SQL 为什么跑不了 （1）下面是”找出人数超过 3 人的职位”，为什么报错？改成正确的写法。 （2）下面是”按职位分组看看每组都有谁、各多少人”，为什么报错？应该怎么改（两条思路）？

  1
  -- (1)
  2
  select job, count(*) from emp where count(*) > 3 group by job;
  3
  
  4
  -- (2)
  5
  select job, name, count(*) from emp group by job;

  提示（先自己想，实在想不出再点开）

  一级 · 思路：两条都撞在”谁能用聚合函数、什么时候能用”这条规则上——第 1 条错在”过滤条件的位置”，第 2 条错在”select 里写了不该写的字段” 二级 · 方法：聚合函数只能出现在 having 或 select 的字段列表里，不能出现在 where；分组之后 select 里只能写分组字段和聚合函数 三级 · 骨架：... group by job ____ count(*) > 3;（第 1 条把条件搬到后面那一段）/ 第 2 条要么删掉 name，要么别分组、改用 where 把某一组的人列出来

  参考答案（做完再点开）

  （1） 报错原文（本机实测）：

  1
  ERROR 1111 (HY000): Invalid use of group function

  原因：where 在分组之前执行，那时 count(*) 还没算出来，没法拿来比较——所以”对聚合结果做判断”必须用 having（它在分组、聚合之后才执行）。正确写法：

  1
  select job, count(*) as cnt from emp group by job having count(*) > 3;

  本机实测得到三组：讲师 12、班主任 6、咨询师 9（having cnt > 3 用 select 里的别名也一样能跑）。

  （2） 报错原文（本机实测）：

  1
  ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db01.emp.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

  原因：按职位分组后，“讲师”这一组有 12 个人，count(*) 是确定的，但 name 该显示哪一个完全说不清——所以严格模式下直接报 1055。两条改法：

  1
  -- 思路一：分组只留分组字段 + 聚合函数
  2
  select job, count(*) from emp group by job;
  3
  
  4
  -- 思路二：不分组，把某一组的人用 where 列出来
  5
  select name from emp where job = 2;

  结论一句话记牢：group by 之后，select 里写的要么是分组字段、要么是聚合函数。

三、综合题#

• 3-1 给员工表做一份”薪资体检报告” 在 db01 库的 emp 表（30 条数据）上，模拟”人事要看一份数据汇总”，按下面的顺序做完并写清每个数字的来历：

  1. 总量：统计员工总数，再统计有多少员工分配了职位——两个数字为什么不一样？差的是谁？（用姓名确认一下）
  2. 薪资总览：平均薪资、最高薪资、最低薪资、薪资总额各是多少？验算一遍平均薪资（用”总额 ÷ 人数”核对，注意这里的”人数”到底该是几）；
  3. 按性别分组：每组的人数、平均薪资分别是多少？
  4. 按职位分组：每种职位各有多少人？（记得会多出一组 NULL）
  5. 先筛后分组再过滤：只看 2015-01-01（含）以前入职的员工，按职位分组统计人数，只保留人数大于等于 2 的职位；
  6. 答一问：如果直接写 select job, name, count(*) from emp group by job; 会怎样？把报错关键信息记下来，并解释”分组后为什么不能查 name”。

  涉及知识点

  知识点	在这里的应用
  聚合函数	第 1-2 步——count / avg / max / min / sum
  null 不参与聚合	第 1 步——count(*) 与 count(job) 的差异；第 2 步——avg 的分母是 29
  分组查询	第 3-4 步——group by gender、group by job（NULL 自成一组）
  where 与 having	第 5 步——先 where 筛行，再 group by，最后 having 筛组
  分组后的字段限制	第 6 步——只能在 select 里写分组字段和聚合函数

  提示（先自己想，实在想不出再点开）

  一级 · 思路：整题就是”聚合 → 分组 → 先筛后分再滤”三层递进；第 1、2 步重点是把 NULL 的影响看清楚，第 3、5 步是分组的常规用法 二级 · 方法：聚合函数 count(*)、count(job)、avg(salary)、max/min/sum；分组 group by 字段；三段式 where ... group by ... having ...；NULL 的两种表现（不进聚合、自成一 组） 三级 · 骨架：第 1 步 select count(*), count(job) from emp;（再用 where job is null 找出那个人）/ 第 5 步 select job, count(*) from emp where entry_date <= '____' group by job ____ count(*) >= 2;

  参考答案（做完再点开）

  1
  -- 1. 总数与"有职位的人数"
  2
  select count(*) as 员工总数, count(job) as 有职位人数 from emp;
  3
  select id, name, job from emp where job is null;
  4
  
  5
  -- 2. 薪资总览
  6
  select avg(salary) as 平均薪资, max(salary) as 最高薪资, min(salary) as 最低薪资, sum(salary) as 薪资总额 from emp;
  7
  select round(avg(salary), 2) from emp;
  8
  
  9
  -- 3. 按性别分组
  10
  select gender, count(*) as 人数, avg(salary) as 平均薪资 from emp group by gender;
  11
  
  12
  -- 4. 按职位分组
  13
  select job, count(*) as 人数 from emp group by job;
  14
  
  15
  -- 5. 先筛、再分组、再过滤
  16
  select job, count(*) as 人数 from emp where entry_date <= '2015-01-01' group by job having count(*) >= 2;
  17
  
  18
  -- 6. 复现报错
  19
  select job, name, count(*) from emp group by job;

  显示更多显示更少

  本机实测结果（MySQL 9.0.1，db01.emp）：

  1. count(*) = 30，count(job) = 29——差的是李云（where job is null 查出来只有他一条）；他没填职位，NULL 不参与聚合，所以没被 count(job) 数到；
  2. 平均薪资 7548.2759、最高 15000（施耐庵）、最低 4700（柴进）、总额 218900；验算：218900 ÷ 29 = 7548.2759——分母是 29 而不是 30，因为李云既没有薪资、也不算进平均（他和第 1 步里的”差的那 1 条”是同一个人，两处现象一个原因）；
  3. 性别分组：

     1
     +--------+-----+-----------+
     2
     | gender | 人数 | 平均薪资   |
     3
     +--------+-----+-----------+
     4
     |      1 |  27 | 7346.1538 |
     5
     |      2 |   3 | 9300.0000 |
     6
     +--------+-----+-----------+

     男性组 27 人的平均薪资分母是 26（李云又”缺席”了一次），这正是 avg 在分组内同样会跳过 NULL 的证据；
  4. 职位分组：

     1
     +------+------+
     2
     | job  | 人数 |
     3
     +------+------+
     4
     |    4 |    1 |
     5
     |    2 |   12 |
     6
     |    3 |    1 |
     7
     |    1 |    6 |
     8
     |    5 |    9 |
     9
     | NULL |    1 |
     10
     +------+------+

     6 组一共 30 人；NULL 一组只有李云一个人——分组时 NULL 是”一类值”，而聚合时它被跳过，这两个表现并不矛盾；
  5. 结果为 2 → 11 人、1 → 5 人、5 → 7 人（比第 4 步的 12、6、9 少，差的就是 2015 年之后入职、被 where 提前筛掉的人）；
  6. 第 6 步会报 ERROR 1055 (42000)，关键信息是 contains nonaggregated column 'db01.emp.name' 和 sql_mode=only_full_group_by——按职位分组后，“讲师”这一组有 12 个人，name 显示谁都不对，所以严格模式下直接拦下。分组查询里 select 能写的只有分组字段 + 聚合函数。