MySQL讲义第 29 讲——select 查询之GROUP BY

使用 GROUP BY 关键字可以将查询结果按照某个字段或多个字段进行分组。分组的依据为 GROUP BY 后面的字段中取值相等的分为一组。GROUP BY 通常与聚合函数一起使用。语法格式如下：

GROUP BY 字段名|表达式 [HAVING 条件表达式] [WITH ROLLUP]说明：
（1）使用分组查询时，select后面的字段列表只能包含 GROUP BY 后面的字段名或表达式以及聚合函数，不能包含其他字段或表达式，否则会报错。
（2）字段名|表达式：分组依据，按字段或表达式进行分组。
（3）HAVING 条件表达式：对分组进行选择，符合条件表达式的结果才会显示。
（4）WITH ROLLUP：在所有记录的最后加上一条记录，该记录为对所有行的统计结果。
（5）可以使用 GROUP_CONCAT() 函数把某个字段中的所有值连接成一个字符串。

一、不带聚合函数的分组查询

1、select 选择的字段列表只包含 GROUP BY 后面的字段名或表达式

mysql> SELECT-> gender-> FROM-> stu-> GROUP BY-> gender;
+--------+
| gender |
+--------+
| 女     |
| 男     |
+--------+
2 rows in set (0.00 sec)

2、select 选择的字段列表包含了其他的字段名或表达式

mysql> SELECT-> stu_name,gender-> FROM -> stu-> GROUP BY-> gender;ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wgx.stu.stu_name' which is not func
tionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

二、使用聚合函数实现分组统计

GROUP BY 通常与聚合函数一起使用，以实现对每一个分组的统计。

1、分别统计男生和女生的平均身高

mysql> SELECT-> gender,avg(height) as 平均身高-> FROM-> stu-> GROUP BY-> gender;
+--------+--------------+
| gender | 平均身高     |
+--------+--------------+
| 女     |    164.80000 |
| 男     |    176.50000 |
+--------+--------------+
2 rows in set (0.00 sec)

2、统计每个系的学生人数

mysql> SELECT-> d.dept_id,-> d.dept_name,-> count(*) as 学生人数-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> GROUP BY -> d.dept_id;
+---------+--------------+--------------+
| dept_id | dept_name    | 学生人数     |
+---------+--------------+--------------+
| D01     | 管理系       |            4 |
| D02     | 计算机系     |            4 |
| D03     | 数学系       |            2 |
+---------+--------------+--------------+
3 rows in set (0.01 sec)

3、统计各个年级的学生人数

mysql> SELECT -> left(stu_id,4),-> count(*) as 学生人数-> FROM-> stu-> GROUP BY-> left(stu_id,4);
+----------------+--------------+
| left(stu_id,4) | 学生人数     |
+----------------+--------------+
| 2018           |            7 |
| 2019           |            4 |
+----------------+--------------+
2 rows in set (0.00 sec)

4、统计各个年级的男生人数

mysql> SELECT -> left(stu_id,4),-> count(*) as 男生人数-> FROM-> stu-> WHERE-> gender = '男'-> GROUP BY-> left(stu_id,4);
+----------------+--------------+
| left(stu_id,4) | 男生人数     |
+----------------+--------------+
| 2018           |            3 |
| 2019           |            3 |
+----------------+--------------+
2 rows in set (0.00 sec)--说明：本例先对 stu 表进行选择，把所有的男生信息取出再按年级进行分组，统计人数。

三、使用 HAVING 对分组进行选择

使用 having 子句可以对分组进行选择。当 having 子句与 where 子句同时使用时，查询执行的顺序为：先使用 where 对表中的记录进行筛选，然后对满足条件的记录分组与统计，再使用 having 子句对分组进行选择。例如：

1、查询学生人数超过 3 个人的系的信息及学生人数

mysql> SELECT -> d.dept_id,-> d.dept_name,-> count(*) as 学生人数-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> GROUP BY-> d.dept_id-> HAVING -> count(*) > 3;
+---------+--------------+--------------+
| dept_id | dept_name    | 学生人数     |
+---------+--------------+--------------+
| D01     | 管理系       |            4 |
| D02     | 计算机系     |            4 |
+---------+--------------+--------------+
2 rows in set (0.01 sec)

2、查询女生人数超过 3 个人的年级及女生的人数

mysql> SELECT -> left(stu_id,4),-> count(*) as 女生人数-> FROM-> stu-> WHERE-> gender = '女'-> GROUP BY-> left(stu_id,4)-> HAVING-> count(*) > 3;
+----------------+--------------+
| left(stu_id,4) | 女生人数     |
+----------------+--------------+
| 2018           |            4 |
+----------------+--------------+
1 row in set (0.00 sec)

3、查询女生平均身高超过 165 的系的信息及女生的平均身高

mysql> SELECT -> d.dept_id,-> d.dept_name,-> avg(height) as 女生平均身高-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> WHERE-> gender = '女'-> GROUP BY-> d.dept_id-> HAVING -> avg(height) > 165;
+---------+--------------+--------------------+
| dept_id | dept_name    | 女生平均身高       |
+---------+--------------+--------------------+
| D01     | 管理系       |          167.00000 |
| D02     | 计算机系     |          167.50000 |
+---------+--------------+--------------------+
2 rows in set (0.00 sec)

4、查询所学课程平均分大于 85分 的学生信息及所学课程的平均分

mysql> SELECT-> s.stu_id,-> s.stu_name,-> avg(score) as 平均分-> FROM-> stu s JOIN score sc ON s.stu_id = sc.stu_id-> GROUP BY-> s.stu_id-> HAVING-> avg(score) > 85;
+-----------+-----------+-----------+
| stu_id    | stu_name  | 平均分    |
+-----------+-----------+-----------+
| 201801102 | 刘国强    |   98.0000 |
| 201801201 | 王艳艳    |   89.0000 |
+-----------+-----------+-----------+
2 rows in set (0.02 sec)

四、按照多个字段分组

例如：查询各个系的男生和女生人数及平均分高

mysql> SELECT -> d.dept_name,-> gender,-> count(*) as 人数,-> avg(height) as 平均分高-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> GROUP BY-> d.dept_id,-> gender;
+--------------+--------+--------+--------------+
| dept_name    | gender | 人数   | 平均分高     |
+--------------+--------+--------+--------------+
| 管理系       | 女     |      1 |    167.00000 |
| 管理系       | 男     |      3 |    178.00000 |
| 计算机系     | 女     |      2 |    167.50000 |
| 计算机系     | 男     |      2 |    175.50000 |
| 数学系       | 女     |      1 |    160.00000 |
| 数学系       | 男     |      1 |    174.00000 |
+--------------+--------+--------+--------------+
6 rows in set (0.01 sec)

五、使用 WITH ROLLUP 参数

在所有记录的最后加上一条记录，该记录为对所有行的统计结果。例如：

SELECT 
coalesce(d.dept_name,'总人数及平均身高') as dept_name,
coalesce(gender,'总人数及平均身高') as gender,
count(*) as 人数,
avg(height) as 平均分高
FROM
stu s JOIN dept d ON s.dept_id = d.dept_id
GROUP BY
d.dept_name,
gender
with rollup;

1、统计每个系的学生人数及平均身高

mysql> SELECT -> d.dept_name,-> count(*) as 人数,-> avg(height) as 平均分高-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> GROUP BY-> d.dept_name-> with rollup;
+--------------+--------+--------------+
| dept_name    | 人数   | 平均分高     |
+--------------+--------+--------------+
| 数学系       |      2 |    167.00000 |
| 管理系       |      4 |    175.25000 |
| 计算机系     |      4 |    171.50000 |
| NULL         |     10 |    172.10000 |
+--------------+--------+--------------+
4 rows in set (0.00 sec)--可以使用 coalesce() 函数为最后一行的第一列指定名称。
--coalesce( ) 函数的作用：返回参数中的第一个非空表达式。
mysql> select coalesce('a','b'),coalesce(null,'c');
+-------------------+--------------------+
| coalesce('a','b') | coalesce(null,'c') |
+-------------------+--------------------+
| a                 | c                  |
+-------------------+--------------------+
1 row in set (0.00 sec)mysql> SELECT -> coalesce(d.dept_name,'总人数') as d.dept_name,-> count(*) as 人数,-> avg(height) as 平均分高-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> GROUP BY-> d.dept_name-> with rollup;
+-----------------------------------+--------+--------------+
| dept_name                         | 人数   | 平均分高     |
+-----------------------------------+--------+--------------+
| 数学系                            |      2 |    167.00000 |
| 管理系                            |      4 |    175.25000 |
| 计算机系                          |      4 |    171.50000 |
| 总人数                            |     10 |    172.10000 |
+-----------------------------------+--------+--------------+
4 rows in set (0.00 sec)

2、查询各个系的男生和女生人数及平均分高

mysql> SELECT -> d.dept_name,-> gender,-> count(*) as 人数,-> avg(height) as 平均分高-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> GROUP BY-> d.dept_name,-> gender-> with rollup;
+--------------+--------+--------+--------------+
| dept_name    | gender | 人数   | 平均分高     |
+--------------+--------+--------+--------------+
| 数学系       | 女     |      1 |    160.00000 |
| 数学系       | 男     |      1 |    174.00000 |
| 数学系       | NULL   |      2 |    167.00000 |
| 管理系       | 女     |      1 |    167.00000 |
| 管理系       | 男     |      3 |    178.00000 |
| 管理系       | NULL   |      4 |    175.25000 |
| 计算机系     | 女     |      2 |    167.50000 |
| 计算机系     | 男     |      2 |    175.50000 |
| 计算机系     | NULL   |      4 |    171.50000 |
| NULL         | NULL   |     10 |    172.10000 |
+--------------+--------+--------+--------------+
10 rows in set (0.00 sec)

为 NULL 值指定内容：

mysql> SELECT -> coalesce(d.dept_name,'总人数及平均身高') as dept_name,-> coalesce(gender,'总人数及平均身高') as gender,-> count(*) as 人数,-> avg(height) as 平均分高-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> GROUP BY-> d.dept_name,-> gender-> with rollup;
+--------------------------+--------------------------+--------+--------------+
| dept_name                | gender                   | 人数   | 平均分高     |
+--------------------------+--------------------------+--------+--------------+
| 数学系                   | 女                       |      1 |    160.00000 |
| 数学系                   | 男                       |      1 |    174.00000 |
| 数学系                   | 总人数及平均身高         |      2 |    167.00000 |
| 管理系                   | 女                       |      1 |    167.00000 |
| 管理系                   | 男                       |      3 |    178.00000 |
| 管理系                   | 总人数及平均身高         |      4 |    175.25000 |
| 计算机系                 | 女                       |      2 |    167.50000 |
| 计算机系                 | 男                       |      2 |    175.50000 |
| 计算机系                 | 总人数及平均身高         |      4 |    171.50000 |
| 总人数及平均身高         | 总人数及平均身高         |     10 |    172.10000 |
+--------------------------+--------------------------+--------+--------------+
10 rows in set, 1 warning (0.00 sec)

六、group_concat( ) 函数的用法

可以使用 GROUP_CONCAT() 函数把某个字段中的所有值连接成一个字符串。和聚合函数的用法相似。格式如下：

group_concat(字段名 | 表达式) 

举例：查询各个年级的学生名单

mysql> SELECT-> left(stu_id,4) as 年级,-> group_concat(stu_name)-> FROM -> stu-> GROUP BY -> left(stu_id,4);
+--------+--------------------------------------------------------------------+
| 年级   | group_concat(stu_name)                                             |
+--------+--------------------------------------------------------------------+
| 2018   | 王占峰,刘国强,巩莉,宋丹风,王艳艳,赵牡丹,王鹏飞                     |
| 2019   | 王宏伟,张静静,李刚,刘鹏                                            |
+--------+--------------------------------------------------------------------+
2 rows in set (0.01 sec)