12｜第十二话：基础篇-MySQL之分组查询

上一话中介绍了一些用来统计数据的聚集函数，我们可以方便的使用这些函数来统计出某列数据的行数、最大值、最小值、平均值以及整列数据的和。但是还有一些问题没有解决，我们来继续向前探索。本篇文章主体脉络：

我们先来看看新的学生信息表：

mysql> show create table student_infoG
*************************** 1. row ***************************
       Table: student_info
Create Table: CREATE TABLE `student_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT '姓名',
  `score` int(11) DEFAULT NULL COMMENT '分数',
  `class` varchar(20) DEFAULT NULL COMMENT '班级',
  `campus` varchar(128) DEFAULT NULL COMMENT '校区',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COMMENT='学生基本信息表'
1 row in set (0.01 sec)

里面放入了一些数据：

mysql> select * from student_info;
+----+-------+-------+--------+-----------+
| id | name  | score | class  | campus    |
+----+-------+-------+--------+-----------+
|  1 | stu1  |   100 | 一班   | 南校区    |
|  2 | stu2  |    85 | 一班   | 南校区    |
|  3 | stu3  |    70 | 二班   | 南校区    |
|  4 | stu4  |    50 | 一班   | 南校区    |
|  5 | stu5  |    88 | 二班   | 南校区    |
|  6 | stu6  |    86 | 三班   | 北校区    |
|  7 | stu7  |    90 | 三班   | 北校区    |
|  8 | stu8  |    50 | 一班   | 南校区    |
|  9 | stu9  |    98 | 二班   | 南校区    |
| 10 | stu10 |    92 | 三班   | 西校区    |
+----+-------+-------+--------+-----------+
10 rows in set (0.00 sec)

可以看到，学生信息表里面有10位学生，后面是对应的分数、班级和该学生所住的校区。

按照我们上一话学习的聚集函数，我们来统计这三个班学生的平均分数，如何来实现呢？由于有三个班，我们需要写三条SQL来计算：

mysql> SELECT AVG(score) FROM student_info where class='一班';
+------------+
| AVG(score) |
+------------+
|    71.2500 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT AVG(score) FROM student_info where class='二班';
+------------+
| AVG(score) |
+------------+
|    85.3333 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT AVG(score) FROM student_info where class='三班';
+------------+
| AVG(score) |
+------------+
|    89.3333 |
+------------+
1 row in set (0.00 sec)

但是当我们有几十个班级的时候，需要统计每个班的平均分，难道要执行几十次吗？这着实有点麻烦，这个痛点可以通过分组来解决。

一、分组

分组就是：针对某个列，将该列的值相同的记录分到一个组中。以我们学生信息表为例，我们就可以按照class这个字段，分为一班、二班、三班，这就是三个分组。

也就是说，class这个列，有多少个不重复的值，就有多少个不同的分组，所以，这种值在设计的时候就要考虑好会可能的范围，一般取值是比较少的，标识这些数据的一个分类，比如这里的班级，就是一个对学生做区别的重要字段。

MySQL提供了GROUP BY子句，后面写上分组列即可，MySQL会帮助我们自动建立分组来方便我们统计信息，具体语句如下：

mysql> SELECT AVG(score),class FROM student_info group by class;
+------------+--------+
| AVG(score) | class  |
+------------+--------+
|    71.2500 | 一班   |
|    89.3333 | 三班   |
|    85.3333 | 二班   |
+------------+--------+
3 rows in set (0.00 sec)

是不是特别的简单？不过，在使用分组的时候必须要意识到，分组的存在仅仅是为了方便我们分别统计各个分组中的信息，所以我们只需要把分组列和聚集函数放到查询列表处就好。如果非分组列出现在查询列表中会出现什么情况呢？比如下边这个查询：

mysql> SELECT AVG(score),class,name FROM student_info group by class;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_mysql.student_info.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

可以看到，这里报错了，我们只是想把记录分为若干组，然后再对各个组分别调用聚集函数去做一些统计工作，但是这里比如查询了name列，由于每一个分组里面有很多学生的名字，MySQL就不知道该如何返回了，自然就会报错。

就像校长给你提了一个需求：统计各班学生的平均分，并且告诉我学生姓名，你会不会觉得莫名其妙？

二、分组和筛选

假设现在有个需求：我们需要对这三个班的平均分做筛选，将平均分大于等于80分的筛选出来展示即可，学校要重点做宣传和奖励，我们该怎么做呢？

可能第一反应是使用where进行筛选不就好了，还用重复讲？

其实这里要做的是，在做完数据分组后，再对分组的统计数据做筛选，我们可能会这么写：

mysql> SELECT AVG(score),class FROM student_info group by class where AVG(score)>=80;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where AVG(score)>=80' at line 1

可以看到，我们在分组之后使用where来筛选的话，会提示报错，原因是这样的写法是不正确的（首先该写法顺序就不对，MySQL会直接报错，具体的顺序要求见第五节），我们先来看下正确的写法，应该是使用having这样的关键字对分组后的数据进行筛选：

mysql> SELECT AVG(score),class FROM student_info group by class having AVG(score)>=80;
+------------+--------+
| AVG(score) | class  |
+------------+--------+
|    89.3333 | 三班   |
|    85.3333 | 二班   |
+------------+--------+
2 rows in set (0.01 sec)

其实，针对分组数据再进行筛选，就得使用HAVING子句，而我们的WHERE实际上仅仅是过滤普通行数据。

比如这样的需求：我们将60分以下的学生剔除，他们分数太低了，然后我们针对这部分学生进行分组计算各班平均分，SQL可以这么写：

mysql> SELECT AVG(score),class FROM student_info where score >= 60 group by class;
+------------+--------+
| AVG(score) | class  |
+------------+--------+
|    92.5000 | 一班   |
|    89.3333 | 三班   |
|    85.3333 | 二班   |
+------------+--------+
3 rows in set (0.00 sec)

在分组之前，先用where将60分以下的学生剔除，不纳入分组计算范围。所以where和having的重要区别是：WHERE子句在分组前进行过滤，作用于每一条记录，WHERE子句过滤掉的记录将不包括在分组中。而HAVING子句在数据分组后进行过滤，作用于整个分组。

我们结合两者使用，我们的需求是：将60分以下的学生剔除后再进行分组计算各班平均分，再将平均分大于等于90分的展示出来，学校要做奖励，SQL写为：

mysql> SELECT AVG(score),class FROM student_info where score >= 60 group by class having AVG(score)>90;
+------------+--------+
| AVG(score) | class  |
+------------+--------+
|    92.5000 | 一班   |
+------------+--------+
1 row in set (0.01 sec)

此时，按照这样的规则，只剩下一班脱颖而出了。

此外值得说明的是，并不是HAVING子句中只能放置在查询列表出现的那些聚集函数，只要是针对这个分组进行统计的聚集函数都可以，比如查询最高分大于95分的该班级的平均分，我们可以这么写：

mysql> SELECT AVG(score),class FROM student_info where score >= 60 group by class having MAX(score)>95;
+------------+--------+
| AVG(score) | class  |
+------------+--------+
|    92.5000 | 一班   |
|    85.3333 | 二班   |
+------------+--------+
2 rows in set (0.01 sec)

这里使用了MAX聚集函数计算每个班的最高分，由于三班并没有学生分数超过95，因此就会被分组过滤掉。

三、分组与排序

如果我们想对各个分组查询出来的统计数据进行排序，需要为查询列表中有聚集函数的表达式添加别名，比如想按照各个学科的平均分从大到小降序排序。

我们这里的一个需求是：我们需要对这三个班的平均分做筛选，将平均分大于等于80分的筛选出来展示并按照平均分倒序排列，学校要重点对第一名做宣传和奖励，我们该怎么做呢？

mysql> SELECT AVG(score) as avg_score,class FROM student_info group by class having AVG(score)>=80 order by avg_score desc;
+-----------+--------+
| avg_score | class  |
+-----------+--------+
|   89.3333 | 三班   |
|   85.3333 | 二班   |
+-----------+--------+
2 rows in set (0.00 sec)

可以看到，按照平均分计算的话，三班的整体平均分是最高的，此时如果我们只想看到一条记录，就可以结合limit限制展示的条数，由于第一名就只会有一个（不考虑重分的情况），所以直接limit 1即可：

mysql> SELECT AVG(score) as avg_score,class FROM student_info group by class having AVG(score)>=80 order by avg_score desc limit 1;
+-----------+--------+
| avg_score | class  |
+-----------+--------+
|   89.3333 | 三班   |
+-----------+--------+
1 row in set (0.00 sec)

四、嵌套分组

有时候按照某个列进行分组太笼统，一个分组内可以被继续划分成更小的分组，这里简单举例，比如我们按照学生宿舍所在校区对学生做分组，可以这么写：

mysql> SELECT AVG(score),campus FROM student_info group by campus;
+------------+-----------+
| AVG(score) | campus    |
+------------+-----------+
|    88.0000 | 北校区    |
|    77.2857 | 南校区    |
|    92.0000 | 西校区    |
+------------+-----------+
3 rows in set (0.00 sec)

我们觉得按照校区分太笼统，继续按照班级分，就可以这么写：

mysql> SELECT AVG(score),campus,class FROM student_info group by campus,class;
+------------+-----------+--------+
| AVG(score) | campus    | class  |
+------------+-----------+--------+
|    88.0000 | 北校区    | 三班   |
|    71.2500 | 南校区    | 一班   |
|    85.3333 | 南校区    | 二班   |
|    92.0000 | 西校区    | 三班   |
+------------+-----------+--------+
4 rows in set (0.01 sec)

如果你乐意，你可以继续把小分组划分成更小的分组。我们只需要在GROUP BY子句中把各个分组列依次写上，用逗号,分隔开就好了。

当然了，这里的举例不是很好，仅仅是演示嵌套分组是个什么，其实没有什么特殊的，就是根据多个列来做分组而已。

五、各子句顺序

如果在一个查询语句中出现了多个子句，那么它们之间的顺序是不能乱放的，顺序如下所示（其中中括号[]中的内容表示可以省略）：

SELECT [DISTINCT] 查询列表
[FROM 表名]
[WHERE 布尔表达式]
[GROUP BY 分组列表 ]
[HAVING 分组过滤条件]
[ORDER BY 排序列表]
[LIMIT 开始行, 限制条数]

这个顺序十分重要！请务必熟记于心！

六、总结

使用分组来统计数据给我们带来了非常大的便利，但是要随时提防有坑的地方：

如果分组列中含有NULL值，那么NULL也会作为一个独立的分组存在。
非分组列不能单独出现在检索列表中(可以被放到聚集函数中)。
WHERE子句和HAVING子句的区别：WHERE子句在分组前进行过滤，作用于每一条记录，WHERE子句过滤掉的记录将不包括在分组中。而HAVING子句在数据分组后进行过滤，作用于整个分组。
如果存在多个分组列，也就是嵌套分组，即按照多个列进行分组。
各子句的顺序不能乱放，请务必熟记于心！