【数据库基础|第5篇】聚合函数与 group by 分组查询详解

前言

上一篇我们学习了 where、order by、limit，已经可以完成比较常见的条件查询、排序查询和分页查询。

但是在真实项目中，查询并不只是把数据查出来展示。很多时候我们还需要做统计。

比如：

• 查询员工总人数
• 查询男性员工和女性员工分别有多少人
• 查询每个部门有多少员工
• 查询员工平均工资
• 查询最高工资和最低工资
• 查询每个部门的平均工资

这些需求就不是简单的 select * from emp 能解决的了。

这时候就需要用到 SQL 中非常重要的内容：聚合函数 和 分组查询。

这一篇我们就来学习 count、sum、avg、max、min 以及 group by 的使用。

一、什么是聚合函数？

聚合函数就是对一组数据进行统计计算的函数。

常见聚合函数如下：

比如员工表中有很多条员工数据，我们可以用聚合函数统计员工总数：

select count(*) from emp;

也可以统计员工平均工资：

select avg salary from emp;

不过上面这句写法是错误的，正确写法应该是：

select avg(salary) from emp;

聚合函数的参数一般要写在括号中。

二、准备员工表和测试数据

为了方便演示，我们继续使用员工表 emp。

create table emp (
id int unsigned primary key auto_increment comment '员工ID',
username varchar(20) not null unique comment '用户名',
name varchar(10) not null comment '姓名',
gender tinyint unsigned comment '性别：1男，2女',
dept_id int unsigned comment '部门ID',
salary decimal(10, 2) comment '工资',
entry_date date comment '入职日期',
create_time datetime comment '创建时间',
update_time datetime comment '修改时间'
) comment '员工表';

准备几条测试数据：

insert into emp(username, name, gender, dept_id, salary, entry_date, create_time, update_time)
values
('zhangsan', '张三', 1, 1, 8000.00, '2024-03-01', now(), now()),
('lisi', '李四', 1, 1, 9500.00, '2024-04-10', now(), now()),
('wangwu', '王五', 2, 2, 7200.00, '2024-05-15', now(), now()),
('zhaoliu', '赵六', 1, 2, 12000.00, '2024-06-01', now(), now()),
('xiaohong', '小红', 2, 3, 8800.00, '2024-06-20', now(), now());

这里多加了一个字段：

dept_id

表示员工所属部门 id。

后面做分组统计时会用到它。

三、count：统计数量

count 用来统计数量。

1. 统计员工总数

select count(*)
from emp;

这条 SQL 会统计 emp 表中一共有多少条员工记录。

2. 给统计结果起别名

直接查询 count(*) 时，结果列名可能不太直观。

可以使用 as 起别名：

select count(*) as total
from emp;

查询结果类似：

这样结果就更清楚。

3. 统计男性员工数量

select count(*) as total
from emp
where gender = 1;

这里先通过 where gender = 1 筛选男性员工，再使用 count(*) 统计数量。

也就是说：

where 先过滤数据
count 再统计过滤后的结果

四、count(*)、count(字段)、count(1) 的区别

在学习 SQL 时，经常会看到几种写法：

count(*)
count(id)
count(1)

它们都能统计数量，但含义有些区别。

1. count(*)

select count(*) from emp;

表示统计表中的记录数。

这是最常见的写法。

2. count(字段)

select count(phone) from emp;

表示统计 phone 字段不为 null 的记录数。

如果某些员工手机号为空，那么 count(phone) 不会统计这些空值。

3. count(1)

select count(1) from emp;

也可以统计记录数。

初学阶段建议优先使用：

count(*)

简单、清楚，也很常见。

五、sum：求和

sum 用来对某个数字字段求和。

1. 统计所有员工工资总和

select sum(salary) as total_salary
from emp;

查询结果类似：

2. 统计某个部门的工资总和

select sum(salary) as total_salary
from emp
where dept_id = 1;

这条 SQL 表示统计部门 id 为 1 的员工工资总和。

先通过 where 过滤部门，再对过滤后的工资求和。

六、avg：求平均值

avg 用来计算平均值。

1. 查询员工平均工资

select avg(salary) as avg_salary
from emp;

查询结果可能是：

MySQL 默认可能会保留较多小数位。

如果想保留两位小数，可以使用 round：

select round(avg(salary), 2) as avg_salary
from emp;

2. 查询女性员工平均工资

select round(avg(salary), 2) as avg_salary
from emp
where gender = 2;

这条 SQL 表示统计女性员工平均工资。

七、max 和 min：最大值和最小值

max 用来求最大值，min 用来求最小值。

1. 查询最高工资

select max(salary) as max_salary
from emp;

2. 查询最低工资

select min(salary) as min_salary
from emp;

3. 查询最早入职日期

select min(entry_date) as earliest_entry_date
from emp;

日期也可以比较大小。

对于日期来说：

越早的日期越小
越晚的日期越大

所以 min(entry_date) 可以查询最早入职日期。

4. 查询最晚入职日期

select max(entry_date) as latest_entry_date
from emp;

八、多个聚合函数一起使用

聚合函数可以一起查询。

比如统计员工总数、平均工资、最高工资、最低工资：

select
count(*) as total,
round(avg(salary), 2) as avg_salary,
max(salary) as max_salary,
min(salary) as min_salary
from emp;

查询结果类似：

这种写法在统计接口中非常常见。

比如后台首页需要展示：

员工总数
平均工资
最高工资
最低工资

就可以通过一条 SQL 查询出来。

九、group by：分组查询

前面的聚合函数都是对整张表或者某个条件过滤后的数据做统计。

但是有时候我们需要按照某个字段分组统计。

比如：

每个性别各有多少员工
每个部门各有多少员工
每个部门平均工资是多少

这时候就需要用到：

group by

1. 按性别统计员工数量

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

查询结果类似：

这条 SQL 的执行逻辑可以理解为：

2. 按部门统计员工数量

select dept_id, count(*) as total
from emp
group by dept_id;

查询结果类似：

这表示：

部门 1 有 2 个员工
部门 2 有 2 个员工
部门 3 有 1 个员工

不过这里只能看到部门 id，还看不到部门名称。

如果想看到部门名称，就需要多表查询。这个内容我们下一篇会详细讲。

十、分组后使用聚合函数

group by 经常和聚合函数一起使用。

1. 统计每个部门平均工资

select dept_id, round(avg(salary), 2) as avg_salary
from emp
group by dept_id;

查询结果类似：

2. 统计每个部门最高工资

select dept_id, max(salary) as max_salary
from emp
group by dept_id;

3. 统计每个部门工资总和

select dept_id, sum(salary) as total_salary
from emp
group by dept_id;

这类统计在后台系统中很常见。

比如部门报表、薪资统计、订单统计、销售统计，本质上都离不开分组查询。

十一、having：对分组后的结果再过滤

where 是对原始数据进行过滤。

having 是对分组统计后的结果进行过滤。

比如我们要查询员工数量大于 1 的部门：

select dept_id, count(*) as total
from emp
group by dept_id
having count(*) > 1;

这条 SQL 的意思是：

where 和 having 的区别

比如查询工资大于 8000 的员工，再按部门统计：

select dept_id, count(*) as total
from emp
where salary > 8000
group by dept_id;

这里的 where salary > 8000 是先过滤员工。

如果要筛选统计后人数大于 1 的部门：

select dept_id, count(*) as total
from emp
group by dept_id
having count(*) > 1;

这里的 having count(*) > 1 是对分组后的结果过滤。

十二、group by 查询中的常见错误

1. select 中乱写非分组字段

错误示例：

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

这条 SQL 的问题是：

name

既不是分组字段，也不是聚合函数字段。

因为按部门分组后，一个部门中可能有多个员工姓名，数据库不知道应该返回哪个 name。

所以更合理的写法是：

select dept_id, count(*) as total
from emp
group by dept_id;

简单记忆：

使用 group by 时，select 后面一般写分组字段和聚合函数。

2. where 中直接使用聚合函数

错误示例：

select dept_id, count(*) as total
from emp
where count(*) > 1
group by dept_id;

这是错误的。

因为 where 是分组前执行的，那个时候还没有 count(*) 的统计结果。

正确写法应该使用 having：

select dept_id, count(*) as total
from emp
group by dept_id
having count(*) > 1;

十三、SQL 执行顺序简单理解

虽然 SQL 写法顺序是：

select ...
from ...
where ...
group by ...
having ...
order by ...
limit ...

但可以简单理解执行逻辑是：

from 找到表
where 过滤原始数据
group by 分组
having 过滤分组结果
select 选择返回字段
order by 排序
limit 分页限制

不需要一开始就死记底层执行流程，但至少要知道：

where 在 group by 前
having 在 group by 后

这样就不容易把 where 和 having 用混。

十四、对应到 Spring Boot + MyBatis 项目

假设我们要写一个统计接口：统计每个性别的员工数量。

1. VO 类设计

public class GenderCountVO {
private Integer gender;
private Long total;

// getter、setter 省略
}

2. Controller 层

@GetMapping("/count/gender")
public Result countByGender() {
List<GenderCountVO> list = empService.countByGender();
return Result.success(list);
}

3. Service 层

@Override
public List<GenderCountVO> countByGender() {
return empMapper.countByGender();
}

4. Mapper 层

@Mapper
public interface EmpMapper {

@Select("select gender, count(*) as total from emp group by gender")
List<GenderCountVO> countByGender();
}

文字说明

这就是一个简单的统计接口。

数据库中通过：

group by gender

按性别分组。

通过：

count(*) as total

统计每组数量。

查询结果封装成 GenderCountVO 返回给前端。

如果前端要画饼图、柱状图，这种接口就很常见。

十五、常见问题总结

1. count(*) 会统计 null 吗？

count(*) 统计的是记录数，不关心某个字段是否为 null。

但是 count(字段) 只统计该字段不为 null 的记录。

2. avg 会不会计算 null？

avg(字段) 会忽略 null 值。

比如有 5 条数据，其中 1 条工资是 null，那么 avg(salary) 只会对非空工资进行平均计算。

3. group by 后 select 能写哪些字段？

一般写：

分组字段
聚合函数

比如：

select dept_id, count(*)
from emp
group by dept_id;

不要随便写没有分组、也没有聚合的普通字段。

4. where 和 having 怎么选？

如果是过滤原始数据，用 where。

如果是过滤分组统计后的结果，用 having。

5. 统计接口返回实体类还是 VO？

建议返回 VO。

比如 GenderCountVO、DeptCountVO、SalaryStatsVO。

因为统计结果不一定和数据库表完全对应，使用 VO 更清楚。

十六、实际开发建议

写聚合统计 SQL 时，可以注意下面几点：

十七、总结

这一篇主要学习了 SQL 中的聚合函数和分组查询。

聚合函数用来对数据进行统计计算，常见的有 count、sum、avg、max、min。

group by 用来按照某个字段分组统计，比如按性别统计人数、按部门统计人数、按部门统计平均工资。

where 和 having 是很容易混淆的两个关键字。where 用来在分组前过滤原始数据，having 用来在分组后过滤统计结果。

这一篇内容已经开始从普通查询走向统计查询了。后面写项目中的首页统计、部门统计、报表接口、图表接口时，基本都会用到这些知识点。

下一篇我们继续学习多表查询，也就是 inner join、left join、right join 的使用。