SQL语句：分组查询

• 在对数据表中数据进行统计时，可能需要按照一定的类别分别进行统计。比如查询每个部门的员工数。

• 使用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)