这是Mysql系列第9篇。
环境：mysql5.7.25，cmd命令中进行演示。
本篇内容
- 分组查询语法
- 聚合函数
- 单字段分组
- 多字段分组
- 分组前筛选数据
- 分组后筛选数据
- where和having的区别
- 分组后排序
- where & group by & having & order by & limit 一起协作
- mysql分组中的坑
- in多列查询的使用
分组查询
语法：
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 | 求和，返回指定列的总和 |
avg | 求平均值，返回指定列数据的平均值 |
分组时，可以使用使用上面的聚合函数。
准备数据
drop table
if exists t_order;
-- 创建订单表
create table
t_order(
id int not
null
AUTO_INCREMENT
COMMENT
'订单id',
user_id bigint not
null comment
'下单人id',
user_name
varchar(
16) not
null
default
'' comment
'用户名',
price
decimal(
10,
2) not
null
default
0 comment
'订单金额',
the_year
SMALLINT not
null comment
'订单创建年份',
PRIMARY
KEY (id)
) comment
'订单表';
-- 插入数据
insert into
t_order(user_id,user_name,price,the_year) values
(
1001,
'路人甲Java',
11.11,
'2017'),
(
1001,
'路人甲Java',
22.22,
'2018'),
(
1001,
'路人甲Java',
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');mysql> select *
from t_order;
+----+---------+---------------+-------+----------+
| id | user_id | user_name | price | the_year |
+----+---------+---------------+-------+----------+
|
1 |
1001 | 路人甲
Java |
11.11 |
2017 |
|
2 |
1001 | 路人甲
Java |
22.22 |
2018 |
|
3 |
1001 | 路人甲
Java |
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)单字段分组
需求：查询每个用户下单数量，输出：用户id、下单数量，如下：
mysql>
SELECT
user_id 用户id,
COUNT(id) 下单数量
FROM
t_order
GROUP
BY user_id;
+----------+--------------+
| 用户id | 下单数量 |
+----------+--------------+
|
1001 |
3 |
|
1002 |
4 |
|
1003 |
2 |
+----------+--------------+
3 rows
in set (
0.00 sec)多字段分组
需求：查询每个用户每年下单数量，输出字段：用户id、年份、下单数量，如下：
mysql>
SELECT
user_id 用户id, the_year 年份,
COUNT(id) 下单数量
FROM
t_order
GROUP
BY user_id , the_year;
+----------+--------+--------------+
| 用户id | 年份 | 下单数量 |
+----------+--------+--------------+
|
1001 |
2017 |
1 |
|
1001 |
2018 |
2 |
|
1002 |
2018 |
3 |
|
1002 |
2019 |
1 |
|
1003 |
2018 |
1 |
|
1003 |
2019 |
1 |
+----------+--------+--------------+
6 rows
in set (
0.00 sec)分组前筛选数据
分组前对数据进行筛选，使用where关键字
需求：需要查询2018年每个用户下单数量，输出：用户id、下单数量，如下：
mysql>
SELECT
user_id 用户id,
COUNT(id) 下单数量
FROM
t_order t
WHERE
t.
the_year =
2018
GROUP
BY user_id;
+----------+--------------+
| 用户id | 下单数量 |
+----------+--------------+
|
1001 |
2 |
|
1002 |
3 |
|
1003 |
1 |
+----------+--------------+
3 rows
in set (
0.00 sec)分组后筛选数据
分组后对数据筛选，使用having关键字
需求：查询2018年订单数量大于1的用户，输出：用户id，下单数量，如下：
方式1：
mysql>
SELECT
user_id 用户id,
COUNT(id) 下单数量
FROM
t_order t
WHERE
t.
the_year =
2018
GROUP
BY user_id
HAVING
count(id)>=
2;
+----------+--------------+
| 用户id | 下单数量 |
+----------+--------------+
|
1001 |
2 |
|
1002 |
3 |
+----------+--------------+
2 rows
in set (
0.00 sec)方式2：
mysql>
SELECT
user_id 用户id,
count(id) 下单数量
FROM
t_order t
WHERE
t.
the_year =
2018
GROUP
BY user_id
HAVING 下单数量>=
2;
+----------+--------------+
| 用户id | 下单数量 |
+----------+--------------+
|
1001 |
2 |
|
1002 |
3 |
+----------+--------------+
2 rows
in set (
0.00 sec)where和having的区别
where是在分组(聚合)前对记录进行筛选，而having是在分组结束后的结果里筛选，最后返回整个sql的查询结果。
可以把having理解为两级查询，即含having的查询操作先获得不含having子句时的sql查询结果表，然后在这个结果表上使用having条件筛选出符合的记录，最后返回这些记录，因此，having后是可以跟聚合函数的，并且这个聚集函数不必与select后面的聚集函数相同。
分组后排序
需求：获取每个用户最大金额，然后按照最大金额倒序，输出：用户id，最大金额，如下：
mysql>
SELECT
user_id 用户id,
max(price) 最大金额
FROM
t_order t
GROUP
BY user_id
ORDER
BY 最大金额 desc;
+----------+--------------+
| 用户id | 最大金额 |
+----------+--------------+
|
1001 |
88.88 |
|
1003 |
66.66 |
|
1002 |
44.44 |
+----------+--------------+
3 rows
in set (
0.00 sec)where & group by & having & order by & limit 一起协作
where、group by、having、order by、limit这些关键字一起使用时，先后顺序有明确的限制，语法如下：
select 列
from
表名
where [查询条件]
group by [分组表达式]
having [分组过滤条件]
order by [排序条件]
limit [offset,] count;注意：
写法上面必须按照上面的顺序来写。
示例：
需求：查询出2018年，下单数量大于等于2的，按照下单数量降序排序，最后只输出第1条记录，显示：用户id，下单数量，如下：
mysql>
SELECT
user_id 用户id,
COUNT(id) 下单数量
FROM
t_order t
WHERE
t.
the_year =
2018
GROUP
BY user_id
HAVING
count(id)>=
2
ORDER
BY 下单数量
DESC
LIMIT
1;
+----------+--------------+
| 用户id | 下单数量 |
+----------+--------------+
|
1002 |
3 |
+----------+--------------+
1 row
in set (
0.00 sec)mysql分组中的坑
本文开头有介绍，分组中select后面的列只能有2种：
- 出现在group by后面的列
- 使用聚合函数的列
oracle、sqlserver、db2中也是按照这种规范来的。
文中使用的是5.7版本，默认是按照这种规范来的。
mysql早期的一些版本，没有上面这些要求，select后面可以跟任何合法的列。
示例
需求：获取每个用户下单的最大金额及下单的年份，输出：用户id，最大金额，年份，写法如下：
mysql> select
user_id 用户id,
max(price) 最大金额, the_year 年份
FROM t_order t
GROUP
BY t.
user_id;
ERROR
1055 (
42000):
Expression #
3
of
SELECT list is not
in
GROUP
BY clause and contains nonaggregated column
'javacode2018.t.the_year' which is not functionally dependent on columns
in
GROUP
BY clause;
this is incompatible
with sql_mode=only_full_group_by上面的sql报错了，原因因为the_year不符合上面说的2条规则(select后面的列必须出现在group by中或者使用聚合函数)，而sql_mode限制了这种规则，我们看一下sql_mode的配置：
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后面的列必须符合上面的说的2点规范。
可以将ONLY_FULL_GROUP_BY去掉，select后面就可以加任意列了，我们来看一下效果。
修改mysql中的my.ini文件：
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，再次运行，效果如下：
mysql> select
user_id 用户id,
max(price) 最大金额, the_year 年份
FROM t_order t
GROUP
BY t.
user_id;
+----------+--------------+--------+
| 用户id | 最大金额 | 年份 |
+----------+--------------+--------+
|
1001 |
88.88 |
2017 |
|
1002 |
44.44 |
2018 |
|
1003 |
66.66 |
2018 |
+----------+--------------+--------+
3 rows
in set (
0.03 sec)看一下上面的数据，第一条88.88的年份是2017年，我们再来看一下原始数据：
mysql> select *
from t_order;
+----+---------+---------------+-------+----------+
| id | user_id | user_name | price | the_year |
+----+---------+---------------+-------+----------+
|
1 |
1001 | 路人甲
Java |
11.11 |
2017 |
|
2 |
1001 | 路人甲
Java |
22.22 |
2018 |
|
3 |
1001 | 路人甲
Java |
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)对比一下，user_id=1001、price=88.88是第3条数据，即the_year是2018年，但是上面的分组结果是2017年，结果和我们预期的不一致，此时mysql对这种未按照规范来的列，乱序了，mysql取的是第一条。
正确的写法，提供两种，如下：
mysql>
SELECT
user_id 用户id,
price 最大金额,
the_year 年份
FROM
t_order t1
WHERE
(t1.
user_id , t1.
price)
IN
(
SELECT
t.
user_id,
MAX(t.
price)
FROM
t_order t
GROUP
BY t.
user_id);
+----------+--------------+--------+
| 用户id | 最大金额 | 年份 |
+----------+--------------+--------+
|
1001 |
88.88 |
2018 |
|
1002 |
44.44 |
2019 |
|
1003 |
66.66 |
2019 |
+----------+--------------+--------+
3 rows
in set (
0.00 sec)
mysql>
SELECT
user_id 用户id,
price 最大金额,
the_year 年份
FROM
t_order t1,(
SELECT
t.
user_id uid,
MAX(t.
price) pc
FROM
t_order t
GROUP
BY t.
user_id) t2
WHERE
t1.
user_id = t2.
uid
AND t1.
price = t2.
pc;
+----------+--------------+--------+
| 用户id | 最大金额 | 年份 |
+----------+--------------+--------+
|
1001 |
88.88 |
2018 |
|
1002 |
44.44 |
2019 |
|
1003 |
66.66 |
2019 |
+----------+--------------+--------+
3 rows
in set (
0.00 sec)上面第1种写法，比较少见，in中使用了多字段查询。
建议：在写分组查询的时候，最好按照标准的规范来写，select后面出现的列必须在group by中或者必须使用聚合函数。
总结
- 在写分组查询的时候，最好按照标准的规范来写，select后面出现的列必须在group by中或者必须使用聚合函数。
- select语法顺序：select、from、where、group by、having、order by、limit，顺序不能搞错了，否则报错。
- in多列查询的使用，下去可以试试
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
