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)