聚合与分组

业务报表很少「一行一行看」，更多是「总共多少、平均多少、每个部门多少」。今天学聚合函数与 GROUP BY。

系列：MySQL 入门到查询 · 第 11 / 17 篇（第 11 天 · 5 月 1 日）
上一篇：日期与时间函数
下一篇：排序与分页
总目录：系列索引

环境说明

• 库：train
• 本篇专用 emp 简化员工表：

use train;

drop table if exists emp;
create table emp(
    empno int,
    ename varchar(20),
    sex varchar(4),
    job varchar(20),
    sal double,
    comm double,
    deptno int
);

insert into emp values
    (1001,'马云','男','经理',20000, 5000, 10),
    (1002,'王明','男','员工', 8000, null, 20),
    (1003,'李梅','女','员工', 7500,  800, 20),
    (1004,'张强','男','总监',30000, null, 30),
    (1005,'赵静','女','经理',18000, 2000, 30),
    (1006,'钱伟','男','员工', 9000, null, 30),
    (1007,'孙丽','女','员工', 7200,  300, 30),
    (1008,'周杰','男','总监',35000, null, 40);

练习数据以本篇 seed 为准。金额为教学假数据。

1. 聚合函数：把一列「竖着算」

普通列：一行一行读
聚合列：整列（或一组）算成一个数

函数	作用
sum(列)	求和
avg(列)	平均值
max(列)	最大值
min(列)	最小值
count(*)	统计行数（含该列为空的行）
count(列)	统计该列非 NULL 的行数

select sum(sal) from emp;
select avg(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select count(*) from emp;

通常结果是 一行一列（一个汇总数字）。

一次查多个聚合

select sum(sal), avg(sal), max(sal), min(sal), count(*) from emp;

可以给别名更好读：

select sum(sal) as 工资总和,
       avg(sal) as 平均工资,
       count(*) as 人数
from emp;

2. count(*) 与 count(列) 的差别

select count(*) from emp;
select count(comm) from emp;
select count(sal) from emp;

写法	统计什么
count(*)	结果集有多少行
count(comm)	comm 列不是 NULL 的有多少行

本篇 emp 里有些 comm 是 null，所以 count(comm) 往往 小于 count(*)。

-- 有奖金的人数：更推荐写清楚条件
select count(*) from emp where comm is not null;

-- 没有奖金的人数
select count(*) from emp where comm is null;

入门建议：「总人数」优先写 count(*)；要统计某列有值的个数，再用 count(列) 或 where + count(*)。
补充：sum / avg 等聚合在计算时一般也会忽略 NULL（本篇 sal 无 NULL，示例不受影响）。

3. 聚合 + WHERE：先筛行，再汇总

select max(sal) from emp where sex='男';
select avg(sal) from emp where deptno=30;
select count(*) from emp where deptno=20;
select sum(sal) from emp where job='员工';

顺序（概念上）	做什么
1. FROM	找到表
2. WHERE	只留下满足条件的行
3. 聚合函数	对这些行的某一列竖着计算

select min(sal) from emp where deptno in (20,30);

4. 重要概念：行列平衡（先避坑）

聚合函数返回的是 1 行；若同时再查「很多行的姓名」，两边行数对不齐。

-- 错误示例（MySQL 8 常见会直接报错）
select ename, sum(sal) from emp;

可能报错（信息较长，大意是）：

... nonaggregated column ... incompatible with sql_mode=only_full_group_by

含义：不能一边显示 8 个人的姓名，一边只给一个工资总和。

错误搭配	为什么
select ename, sum(sal) from emp	姓名多行 vs 总和一行
select ename, sex from emp group by sex	姓名多行 vs 性别分组后只有几行

正确做法（入门）

1. 只聚合，不带无关明细列：select sum(sal) from emp;
2. 用了 group by，select 里只放：分组列 + 聚合函数（见下一节）

不同 MySQL 版本对「违规 SQL」的严格程度不同；不要依赖宽松模式，按规范写。

5. GROUP BY：按某一列「合并同类项」

select sex from emp group by sex;
select deptno from emp group by deptno;
select job from emp group by job;

写法	结果
group by sex	性别去重后的组（如：男、女）
group by deptno	部门编号去重（10、20、30、40…）

分组 + 聚合（最常用）

select deptno, sum(sal) from emp group by deptno;
select deptno, avg(sal) from emp group by deptno;
select deptno, max(sal) from emp group by deptno;
select deptno, min(sal) from emp group by deptno;
select deptno, count(*) from emp group by deptno;

读法：先按 deptno 分组，再对每一组分别算聚合。

select sex, count(*) from emp group by sex;
select job, count(*) from emp group by job;
select deptno, sum(sal), avg(sal), count(*) from emp group by deptno;

多列一起分组

把多列当成一个整体组合：

select deptno, sex, count(*) from emp group by deptno, sex;
select deptno, job, max(sal) from emp group by deptno, job;

规则：select 里出现的「非聚合列」，一般都要出现在 group by 列表里（或能唯一对应到分组）。

6. HAVING：对「分组后的结果」再筛

WHERE 和 HAVING 分工

子句	时机	典型条件
WHERE	分组之前，对原始行	deptno in (20,30)、sex='男'
HAVING	分组之后，对组结果	count(*)>3、avg(sal)>10000

-- 先限定部门范围，再分组统计人数
select deptno, count(*)
from emp
where deptno in (20,30)
group by deptno;

-- 每个部门的平均工资（先分组，再过滤「人数」）
select deptno, avg(sal)
from emp
group by deptno
having count(*) >= 2;

聚合条件不能放 WHERE

-- 错误：聚合条件写在 where
select deptno, avg(sal) from emp where count(*)>2 group by deptno;

常见报错：

ERROR 1111 (HY000): Invalid use of group function

正确：

select deptno, avg(sal)
from emp
group by deptno
having count(*) > 2;

普通条件放哪更合适？

-- 推荐：普通条件 → where
select deptno, count(*)
from emp
where comm is not null
group by deptno;

-- having 里写「非分组、非聚合」的列，MySQL 8 可能因 ONLY_FULL_GROUP_BY 报错
-- select deptno, count(*) from emp group by deptno having comm is not null;

入门口诀

普通行条件 → WHERE
组结果/聚合条件 → HAVING

HAVING 示例

-- 平均工资高于某个值的部门
select deptno, avg(sal) as avg_sal
from emp
group by deptno
having avg(sal) > 10000;

-- 人数至少 2 人的部门，显示部门与人数
select deptno, count(*) as cnt
from emp
group by deptno
having count(*) >= 2;

7. 完整跟练脚本

use train;

drop table if exists emp;
create table emp(
    empno int,
    ename varchar(20),
    sex varchar(4),
    job varchar(20),
    sal double,
    comm double,
    deptno int
);
insert into emp values
    (1001,'马云','男','经理',20000, 5000, 10),
    (1002,'王明','男','员工', 8000, null, 20),
    (1003,'李梅','女','员工', 7500,  800, 20),
    (1004,'张强','男','总监',30000, null, 30),
    (1005,'赵静','女','经理',18000, 2000, 30),
    (1006,'钱伟','男','员工', 9000, null, 30),
    (1007,'孙丽','女','员工', 7200,  300, 30),
    (1008,'周杰','男','总监',35000, null, 40);

select sum(sal), avg(sal), max(sal), min(sal), count(*) from emp;
select count(*) from emp;
select count(comm) from emp;
select count(*) from emp where comm is not null;

select max(sal) from emp where sex='男';
select count(*) from emp where deptno=30;

select deptno, sum(sal), avg(sal), count(*)
from emp
group by deptno;

select sex, count(*) from emp group by sex;
select deptno, sex, count(*) from emp group by deptno, sex;

select deptno, count(*)
from emp
where deptno in (20,30)
group by deptno;

select deptno, avg(sal)
from emp
group by deptno
having count(*) >= 2;

select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > 10000;

8. 速查表

目标	写法
总和	select sum(列) from 表;
平均	avg(列)
最大/最小	max(列) / min(列)
行数	count(*)
某列非空行数	count(列)
分组统计	select 分组列, 聚合 from 表 group by 分组列;
多列分组	group by 列1, 列2
行条件	where ... group by ...
组条件	group by ... having 聚合条件
不要混	普通行条件不要写 where count(*)...

9. 今天的练习清单

• sum/avg/max/min/count(*) 各一句
• 对比 count(*) 与 count(comm)
• where 过滤后再聚合（如 30 号部门平均工资）
• group by deptno + 各聚合
• group by deptno, sex 多列分组
• having count(*)>=2
• 试错误语句 select ename, sum(sal) from emp;，读懂报错含义
• 试 where count(*)>2，体会为何必须放 having

常见错误

易错：select 混放明细列与聚合列，又不 group by（行列不平衡）。
易错：聚合条件写进 where。
易错：having 里用未分组且非聚合的列。
易错：count(列) 想当总人数，但该列有 NULL。
易错：多列分组时，select 漏写了分组列或多写了无关列。
易错：group by 后以为每组只有一行原始数据——其实是每组一行汇总（通常）。

小结

1. 聚合函数把一列（或一组）算成汇总值：sum/avg/max/min/count。
2. count(*) 数行；count(列) 跳过 NULL。
3. WHERE 过滤原始行；HAVING 过滤分组结果；聚合条件放 HAVING。
4. 有 group by 时，select 主要写：分组列 + 聚合函数。
5. 下一篇 ORDER BY + LIMIT：把结果排序并取前 N 条。

---

系列导航：总目录 · 上一篇：日期与时间函数 · 下一篇：排序与分页