玩转Mysql系列 - 第9篇：分组查询详解（group by & having）

group_function：聚合函数。

group_by_expression：分组表达式，多个之间用逗号隔开。

group_condition：分组之后对数据进行过滤。

分组中，select后面只能有两种类型的列：

1. 出现在group by后的列

2. 或者使用聚合函数的列

聚合函数

| 函数名称 | 作用 |

| — | — |

| 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种：

1. 出现在group by后面的列

2. 使用聚合函数的列

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)

最后

整理的这些资料希望对Java开发的朋友们有所参考以及少走弯路，本文的重点是你有没有收获与成长，其余的都不重要，希望读者们能谨记这一点。

其实面试这一块早在第一个说的25大面试专题就全都有的。以上提及的这些全部的面试+学习的各种笔记资料，我这差不多来回搞了三个多月，收集整理真的很不容易，其中还有很多自己的一些知识总结。正是因为很麻烦，所以对以上这些学习复习资料感兴趣，

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)

最后

整理的这些资料希望对Java开发的朋友们有所参考以及少走弯路，本文的重点是你有没有收获与成长，其余的都不重要，希望读者们能谨记这一点。

[外链图片转存中…(img-z8hH8kPT-1714759620913)]

[外链图片转存中…(img-SnHR8bBV-1714759620914)]

其实面试这一块早在第一个说的25大面试专题就全都有的。以上提及的这些全部的面试+学习的各种笔记资料，我这差不多来回搞了三个多月，收集整理真的很不容易，其中还有很多自己的一些知识总结。正是因为很麻烦，所以对以上这些学习复习资料感兴趣，

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录