写在前面

在学习了前面的order by和limit分页查询之后，接下来开始学习另一个较为高级的用法—分组查询，这个在实际工作中用到的频率也是较大的。

本篇是DQL的分组查询篇，主要学习的内容如下所示：（1）分组查询基本语法格式；（2）聚合函数；（3）单个字段分组；（4）多个字段分组；（5）分组前筛选数据；（6）分组后筛选数据；（7）where和having的区别；（8）分组后排序；（9）综合查询；（10）分组查询中的坑；（11）文章小结等。

需要说明的是，本篇文章代码中被[]包含的内容表示可选的，而被|分割的内容表示可选其一。

分组查询

基本语法格式

分组查询的基本语法格式如下所示：

1
2
3
4
select column,group_function,... from table
[where condition]
group by group_by_expression
[having group_condition];

解释一下上述代码的含义，group_function是聚合函数，group_by_expression是分组表达式，如果存在多个，那么需要使用逗号进行隔开；group_condition是对分组之后的数据进行过滤。在分组中，select后面只能有两种类型的列：一是出现在group by之后的列；二是使用聚合函数的列，除此之外不得出现任何列。

聚合函数

在进行分组之后，可以先使用以下聚合函数：

函数名称 作用
max 返回指定列中的最大值
min 返回指定列中的最小值
count 返回统计查询结果的行数
sum 求和，返回指定列的总和
count 返回统计查询结果的行数
avg 求平均值，返回指定列数据的平均值

准备数据

考虑到后续学习的必要，这里先准备好测试数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
mysql> drop table if exists t_order;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_order(
    -> id int not null primary key auto_increment comment '订单id',
    -> user_id bigint not null comment '下单人id',
    -> user_name varchar(18) not null default '' comment '用户名',
    -> price decimal(10,2) not null default 0 comment '订单金额',
    -> create_year smallint not null comment '订单创建年份')comment '订单表';
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t_order(user_id,user_name,price,create_year)values
    -> (1001,'余思',11.11,'2018'),
    -> (1001,'余思',22.22,'2019'),
    -> (1001,'余思',88.88,'2018'),
    -> (1002,'思录',33.33,'2018'),
    -> (1002,'思录',12.22,'2018'),
    -> (1002,'思录',16.66,'2018'),
    -> (1002,'思录',44.44,'2019'),
    -> (1003,'天涯',55.55,'2018'),
    -> (1003,'天涯',66.66,'2019');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from t_order;
+----+---------+-----------+-------+-------------+
| id | user_id | user_name | price | create_year |
+----+---------+-----------+-------+-------------+
|  1 |    1001 | 余思      | 11.11 |        2018 |
|  2 |    1001 | 余思      | 22.22 |        2019 |
|  3 |    1001 | 余思      | 88.88 |        2018 |
|  4 |    1002 | 思录      | 33.33 |        2018 |
|  5 |    1002 | 思录      | 12.22 |        2018 |
|  6 |    1002 | 思录      | 16.66 |        2018 |
|  7 |    1002 | 思录      | 44.44 |        2019 |
|  8 |    1003 | 天涯      | 55.55 |        2018 |
|  9 |    1003 | 天涯      | 66.66 |        2019 |
+----+---------+-----------+-------+-------------+
9 rows in set (0.00 sec)

单个字段分组

例子1：下面的例子展示了，如何进行单个字段的分组。

查询每个用户的下单数量，并且输出用户id和下单数量，如下所示：

1
2
3
4
5
6
7
8
9
mysql> select user_id as 用户id,count(id) as 下单数量 from t_order group by 用户id;
+----------+--------------+
| 用户id   | 下单数量     |
+----------+--------------+
|     1001 |            3 |
|     1002 |            4 |
|     1003 |            2 |
+----------+--------------+
3 rows in set (0.00 sec)

多个字段分组

例子2：下面的例子展示了，如何进行多个字段的分组。

查询每个用户每年的下单数量，并且输出用户id、年份和下单数量，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select user_id as 用户id,create_year as 年份,count(id) as 下单数量 from t_order group by 用户id,年份;
+----------+--------+--------------+
| 用户id   | 年份   | 下单数量     |
+----------+--------+--------------+
|     1001 |   2018 |            2 |
|     1001 |   2019 |            1 |
|     1002 |   2018 |            3 |
|     1002 |   2019 |            1 |
|     1003 |   2018 |            1 |
|     1003 |   2019 |            1 |
+----------+--------+--------------+
6 rows in set (0.00 sec)

分组前筛选数据

有时候我们需要在分组前对数据进行筛选，此时就需要使用到where关键字。

例子3：下面的例子展示了，如何在分组前来筛选数据。

查询2018年每个用户的下单数量，并且输出用户id和下单数量，如下所示：

1
2
3
4
5
6
7
8
9
mysql> select user_id as 用户id,count(id) as 下单数量 from t_order where create_year=2018  group by 用户id;
+----------+--------------+
| 用户id   | 下单数量     |
+----------+--------------+
|     1001 |            2 |
|     1002 |            3 |
|     1003 |            1 |
+----------+--------------+
3 rows in set (0.00 sec)

分组后筛选数据

我们除了可以在分组前筛选数据，其实还可以在分组后筛选数据，此时就需要使用到having关键字。

例子4：下面的例子展示了，如何在分组后来筛选数据。

查询2018年订单数量大于1的用户信息，并且输出用户id和下单数量，那么此时SQL语句如下所示：

1
2
3
4
5
6
7
8
mysql> select user_id as 用户id,count(id) as 下单数量 from t_order where create_year =2018 group by 用户id having 下单数量 >1;
+----------+--------------+
| 用户id   | 下单数量     |
+----------+--------------+
|     1001 |            2 |
|     1002 |            3 |
+----------+--------------+
2 rows in set (0.00 sec)

where和having的区别

where字句是在分组（聚合）前对记录进行筛选，where排除的记录不再包括在分组中；而having则是在分组结束后的结果中筛选，最后返回满足条件的查询结果。

开发者可以将having理解为两级查询，即含having的查询操作，首先获得不含having子句时的sql查询结果表，其次在这个结果表的基础上使用having条件来筛选出符合条件的记录，最后返回这些记录。因此having后面可以跟聚合函数，并且这个聚合函数不一定要与select后面的聚合函数相同。

分组后排序

在实际工作中经常会遇到分组后排序的需求，因此这个也需要掌握。

例子5：下面的例子展示了，如何在分组后进行排序。

获取每个用户下单金额最高的那一单信息，然后按照金额倒序，并且输出用户id和最大金额，那么此时SQL语句如下所示：

1
2
3
4
5
6
7
8
9
mysql> select user_id as 用户id,max(price) as 最大金额 from t_order group by 用户id order by 最大金额 desc;
+----------+--------------+
| 用户id   | 最大金额     |
+----------+--------------+
|     1001 |        88.88 |
|     1003 |        66.66 |
|     1002 |        44.44 |
+----------+--------------+
3 rows in set (0.00 sec)

注意必须是先分组，然后才允许排序，否则程序会抛异常。

综合查询

接下来将通过一个例子，来将之前介绍的where、group by、order by、having和limit等关键字一起使用，但是在此之前需要注意它们之间的使用顺序：

1
2
3
4
5
6
select 列名 from 表名
where [查询条件]
group by [分组表达式]
having [分组过滤条件]
order by [排序条件]
limit [offset,] count;

当使用上述语句中的几个或者全部时，必须按照前面所列举的顺序来书写SQL语句。

例子6：下面的例子展示了，如何进行综合查询。

查询出2018年，下单数量大于1的，按照下单数量降序，最后只输出第一条记录，并且显示用户id和下单数量，那么此时SQL语句如下所示：

1
2
3
4
5
6
7
mysql> select user_id as 用户id,count(id) as 下单数量 from t_order where create_year=2018 group by 用户id having  下单数量 >1 order by 下单数量 desc limit 1;
+----------+--------------+
| 用户id   | 下单数量     |
+----------+--------------+
|     1002 |            3 |
+----------+--------------+
1 row in set (0.00 sec)

分组查询中的坑

接下来介绍的是分组查询中可能会遇到的坑，前面说过在分组中，select后面的列只能有2种：一是出现在group by之后的列；二是使用聚合函数的列，除此之外不得出现任何列。这其实是一个规范，Oracle、SQLserver和DB2也是采用这种规范。笔者使用的是MySQL5.7的版本，默认也是按照这个规范来的。但是MySQL早期的版本没有上面这个要求，select后面可以跟任何合法的列。

例子7：下面的例子展示了分组查询中的坑。

查询出每个用户下单的最大金额及下单的年份信息，并且显示用户id、最大金额和下单年份，那么此时SQL语句如下所示：

1
2
mysql> select user_id as 用户id,max(price) as 最大金额,create_year as 下单年份 from t_order group by 用户id;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'envybook.t_order.create_year' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

可以看到上面的SQL执行抛出异常，原因在于create_year并不满足前面所说的两种情况（select后面的列必须出现在group by或者聚合函数中），而sql_mode限制了这种规则，开发者可以使用如下命令来查看sql_mode的配置，如下所示：

1
2
3
4
5
6
7
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到这个sql_mode中包含了ONLY_FULL_GROUP_BY，它就表示select后面的列必须符合前面所说的两种情况。

开发者可以将其中的ONLY_FULL_GROUP_BY删除，这样select后面的列就没有任意限制了。修改MySQL的my.ini文件，将其修改为如下所示信息：

1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

之后重启MySQL服务，然后再次运行如下命令，可以得到如下结果：

1
2
3
4
5
6
7
8
9
mysql> select user_id as 用户id,max(price) as 最大金额,create_year as 下单年份 from t_order group by 用户id;
+----------+--------------+--------------+
| 用户id   | 最大金额     | 下单年份     |
+----------+--------------+--------------+
|     1001 |        88.88 |         2018 |
|     1002 |        44.44 |         2018 |
|     1003 |        66.66 |         2018 |
+----------+--------------+--------------+
3 rows in set (0.00 sec)

查看一下原始数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select * from t_order order by user_id asc,price desc;
+----+---------+-----------+-------+-------------+
| id | user_id | user_name | price | create_year |
+----+---------+-----------+-------+-------------+
|  3 |    1001 | 余思      | 88.88 |        2018 |
|  2 |    1001 | 余思      | 22.22 |        2019 |
|  1 |    1001 | 余思      | 11.11 |        2018 |
|  7 |    1002 | 思录      | 44.44 |        2019 |
|  4 |    1002 | 思录      | 33.33 |        2018 |
|  6 |    1002 | 思录      | 16.66 |        2018 |
|  5 |    1002 | 思录      | 12.22 |        2018 |
|  9 |    1003 | 天涯      | 66.66 |        2019 |
|  8 |    1003 | 天涯      | 55.55 |        2018 |
+----+---------+-----------+-------+-------------+
9 rows in set (0.00 sec)

神奇的事情发生了，下单金额为66.66和44.44的记录年份应该都是2019年，也就是id分别为9和7的记录，但是很明显上面的分组结果却是2018年，结果和预期的不一致，那是因为MySQL对于这种未按照规定来的记录，不知道应该返回什么值。

那么应该如何实现我们的需求：查询出每个用户下单的最大金额及下单的年份信息，并且显示用户id、最大金额和下单年份？这里提供两种方法。

方法1：使用IN关键字配合嵌套查询，此时SQL语句如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select t1.user_id as 用户id, t1.price as 最大金额,t1.create_year as 下单年份
    -> from t_order as t1
    -> where (t1.user_id,t1.price)
    -> in
    -> (select t2.user_id,max(t2.price) from t_order as t2 group by t2.user_id);
+----------+--------------+--------------+
| 用户id   | 最大金额     | 下单年份     |
+----------+--------------+--------------+
|     1001 |        88.88 |         2018 |
|     1002 |        44.44 |         2019 |
|     1003 |        66.66 |         2019 |
+----------+--------------+--------------+
3 rows in set (0.00 sec)

其实上面就是使用了自连接查询。自连接查询是指在一个连接查询中，涉及的两个表都是同一个表。前面我们发现只有年份信息是错误的，而用户id和最大金额都是准确的，因此可以先查询到正确的用户id和最大金额，然后再通过用户id来查询得到正确的年份信息，最后拼接成正确的信息进行返回。

不过这种方式在实际工作中用的不是很多，不建议使用。

方法2：使用where字句配合嵌套查询，此时SQL语句如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select t1.user_id as 用户id, t1.price as 最大金额,t1.create_year as 下单年份
    -> from t_order as t1,
    -> (select t.user_id as tuid,max(t.price) as p
    -> from t_order as t
    -> group by t.user_id) as t2
    -> where t1.user_id = t2.tuid and t1.price =t2.p;
+----------+--------------+--------------+
| 用户id   | 最大金额     | 下单年份     |
+----------+--------------+--------------+
|     1001 |        88.88 |         2018 |
|     1002 |        44.44 |         2019 |
|     1003 |        66.66 |         2019 |
+----------+--------------+--------------+
3 rows in set (0.00 sec)

第二种方法其实本质上和第一种是没有区别的，但是似乎更容易理解。

因此建议在写分组查询的时候，尽量按照标准规范来书写，即select后面的列只能有2种：一是出现在group by之后的列；二是使用聚合函数的列，除此之外不得出现任何列。

文章小结

接下来将对本文的一些知识进行小结：
（1）在写分组查询的时候，尽量按照标准规范来书写，即select后面的列只能有2种：一是出现在group by之后的列；二是使用聚合函数的列，除此之外不得出现任何列；
（2）注意在进行综合查询的时候，select语句的语法顺序为：select、from、where、group by、having、order by、limit，顺序一定不能搞错，否则程序就会抛异常。

那么这样关于DQL的分组查询篇学习就到此为止，后续开始学习其他知识。