-
在对数据表中数据进行统计时，可能需要按照一定的类别分别进行统计。比如查询每个部门的员工数。
-
使用GROUP BY按某个字段，或者多个字段中的值，进行分组，字段中值相同的为一组
语法格式
- 查询列表必须是分组函数和出现在GROUP BY后面的字段
- 通常而言，分组前的数据筛选放在where子句中，分组后的数据筛选放在having子句中
SELECT 字段名1(要求出现在group by后面)，分组函数(),……
FROM 表名
WHERE 条件
GROUP BY 字段名1，字段名2
ORDER BY 字段
HAVING 过滤条件;
应用实例
- 查询每个部门的人数
mysql> select dept_id, count(*) from employees group by dept_id;
+---------+----------+
| dept_id | count(*) |
+---------+----------+
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 4 | 55 |
| 5 | 12 |
| 6 | 9 |
| 7 | 35 |
| 8 | 3 |
+---------+----------+
8 rows in set (0.00 sec)
- 查询每个部门中年龄最大的员工
mysql> select dept_id, min(birth_date) from employees group by dept_id;
+---------+-----------------+
| dept_id | min(birth_date) |
+---------+-----------------+
| 1 | 1971-08-19 |
| 2 | 1971-11-02 |
| 3 | 1971-09-09 |
| 4 | 1972-01-31 |
| 5 | 1971-08-14 |
| 6 | 1973-04-14 |
| 7 | 1971-12-10 |
| 8 | 1989-05-19 |
+---------+-----------------+
8 rows in set (0.00 sec)
- 查询每个部门入职最晚员工的入职时间
mysql> select dept_id, max(hire_date) from employees group by dept_id;
+---------+----------------+
| dept_id | max(hire_date) |
+---------+----------------+
| 1 | 2018-11-21 |
| 2 | 2018-09-03 |
| 3 | 2019-07-04 |
| 4 | 2021-02-04 |
| 5 | 2019-06-08 |
| 6 | 2017-10-07 |
| 7 | 2020-08-21 |
| 8 | 2019-11-14 |
+---------+----------------+
8 rows in set (0.00 sec)
- 统计各部门使用@guodong.com邮箱的员工人数
mysql> select dept_id, count(*) from employees where email like '%@guodong.com' group by dept_id;
+---------+----------+
| dept_id | count(*) |
+---------+----------+
| 1 | 5 |
| 2 | 2 |
| 3 | 4 |
| 4 | 32 |
| 5 | 7 |
| 6 | 5 |
| 7 | 15 |
| 8 | 1 |
+---------+----------+
8 rows in set (0.00 sec)
- 查看员工2018年工资总收入，按总收入进行降序排列
mysql> select employee_id, sum(basic+bonus) as total from salary where year(date)=2018 group by employee_id order by total desc;
- 查询部门人数少于5人
mysql> select dept_id, count(*) from employees where count(*)<10 group by dept_id;
ERROR 1111 (HY000): Invalid use of group function
mysql> select dept_id, count(*) from employees group by dept_id having count(*)<10;
+---------+----------+
| dept_id | count(*) |
+---------+----------+
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 6 | 9 |
| 8 | 3 |
+---------+----------+
5 rows in set (0.00 sec) 作者：一个小运维
链接：https://www.jianshu.com/p/75d3e7d52c34
相关资源：