MySQL系列复习（9）分组查询（group by、having）

2023年7月29日12:05:23

环境：mysql5.7.30，cmd命令中进⾏演⽰。

目录

1、分组查询语法

2、聚合函数

3、准备数据

4、单字段分组

5、多字段分组

6、分组前筛选数据

7、分组后筛选数据

8、 where和having的区别

9、分组后排序

10、where & group by & having & order by & limit 一起协作

11、mysql分组中的坑

12、总结

1、分组查询语法

语法：

SELECT column,group_function,..... FROM table

[WHERE condition]

GROUP BY group_by_expression

[HAVING group_condition];

说明：

  • graph_function：聚合函数；
  • group_by_expression：分组表达式，多个之间用逗号隔开；
  • group_condition：分支之后对数据进行过滤；
  • 分组之后，select后面只能有两种类型的列：1）出现在group  by后的列；2）或者使用聚合函数的列。


2、聚合函数

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

 

 

 

 

 

 

 

分组时，可以使用上面的聚合函数。

3、准备数据

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,'myron Java',11.11,'2017'),
(1001,'myron Java',22.22,'2018'),
(1001,'myron 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 | myron Java | 11.11 |     2017 |
|  2 |    1001 | myron Java | 22.22 |     2018 |
|  3 |    1001 | myron 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)

mysql>

4、单字段分组

需求：查询每个用户下单数量，输出：用户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.04 sec)

mysql>

5、多字段分组

需求：查询每个用户每年下单数量，输出字段：用户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)

mysql>

6、分组前筛选数据

分组前对数据进行筛选，使用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)

mysql>

7、分组后筛选数据

分组后对数据筛选，使用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)

mysql>

8、 where和having的区别

where是在分组（聚合）前对记录进行筛选， 而having是在分组结束后的结果里筛选，最后返回整个sql的查询结果。

可以把having理解为两级查询，即含having的查询操作先获得不含having子句时的sql查询结果表，然后在这个结果表上使用having条件筛选出符合的记录，最后返回这些记录，因此，having后是可以跟聚合函数的，并且这个聚合函数不必与select后面的聚集函数相同。

9、分组后排序

需求：获取每个用户最大金额，然后按照最大金额倒序，输出：用户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)

mysql>

10、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 * from t_order;
+----+---------+------------+-------+----------+
| id | user_id | user_name  | price | the_year |
+----+---------+------------+-------+----------+
|  1 |    1001 | myron Java | 11.11 |     2017 |
|  2 |    1001 | myron Java | 22.22 |     2018 |
|  3 |    1001 | myron 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)

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>

11、mysql分组中的坑

本文开头有介绍，分组中select后面的列只能是2种：

  • 1）出现在group by后面的列；
  • 2）使用聚合函数的列。

Oracle、SqlServer、db2中也是按照这种规范来的。

文中使用的是5.7版本，默认是按照这种规范来的。

mysql早期的一些版本，没有上面这些要求，select后面可以跟任何合法的列。

建议：在写分组查询的时候，最好按照标准的规范来写，select后面出现的列必须在group by中或者必须使用聚合函数

12、总结

1）在写分组查询的时候，，最好按照标准的规范来写，select后出现的列必须在group by中或者必须使用聚合函数。

2）select语法顺序：select、from、where、group by、having、order by、limit，顺序不能搞错了，否则报错。

  • 作者：一棵树～
  • 原文链接：https://blog.csdn.net/MyronCham/article/details/109741534
    更新时间：2023年7月29日12:05:23 ，共 4854 字。