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

news/2024/11/24 13:31:38/

这是Mysql系列第9篇。

环境：mysql5.7.25，cmd命令中进行演示。

本篇内容

  1. 分组查询语法

  2. 聚合函数

  3. 单字段分组

  4. 多字段分组

  5. 分组前筛选数据

  6. 分组后筛选数据

  7. where和having的区别

  8. 分组后排序

  9. where & group by & having & order by & limit 一起协作

  10. mysql分组中的坑

  11. 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后面只能有两种类型的列：

  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) 下单数量FROMt_orderGROUP 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) 下单数量FROMt_orderGROUP 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) 下单数量FROMt_order tWHEREt.the_year = 2018GROUP BY user_id;
+----------+--------------+
| 用户id   | 下单数量     |
+----------+--------------+
|     1001 |            2 |
|     1002 |            3 |
|     1003 |            1 |
+----------+--------------+
3 rows in set (0.00 sec)

分组后筛选数据

分组后对数据筛选，使用having关键字

需求：查询2018年订单数量大于1的用户，输出：用户id，下单数量，如下：

方式1：

mysql> SELECTuser_id 用户id, COUNT(id) 下单数量FROMt_order tWHEREt.the_year = 2018GROUP BY user_idHAVING count(id)>=2;
+----------+--------------+
| 用户id   | 下单数量     |
+----------+--------------+
|     1001 |            2 |
|     1002 |            3 |
+----------+--------------+
2 rows in set (0.00 sec)

方式2：

mysql> SELECTuser_id 用户id, count(id) 下单数量FROMt_order tWHEREt.the_year = 2018GROUP BY user_idHAVING 下单数量>=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> SELECTuser_id 用户id, max(price) 最大金额FROMt_order tGROUP BY user_idORDER 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> SELECTuser_id 用户id, COUNT(id) 下单数量FROMt_order tWHEREt.the_year = 2018GROUP BY user_idHAVING count(id)>=2ORDER BY 下单数量 DESCLIMIT 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> selectuser_id 用户id, max(price) 最大金额, the_year 年份FROM t_order tGROUP 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> selectuser_id 用户id, max(price) 最大金额, the_year 年份FROM t_order tGROUP 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> SELECTuser_id 用户id,price 最大金额,the_year 年份FROMt_order t1WHERE(t1.user_id , t1.price)IN(SELECTt.user_id, MAX(t.price)FROMt_order tGROUP 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> SELECTuser_id 用户id,price 最大金额,the_year 年份FROMt_order t1,(SELECTt.user_id uid, MAX(t.price) pcFROMt_order tGROUP BY t.user_id) t2WHEREt1.user_id = t2.uidAND  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中或者必须使用聚合函数。

总结

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

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

  3. in多列查询的使用，下去可以试试


http://www.ppmy.cn/news/1067104.html

相关文章

postgresql 性能调优

postgresql 性能调优

性能调优是为了提高 PostgreSQL 数据库的性能和响应速度。下面是一些常见的 PostgreSQL 性能调优技巧： 1 确保合适的硬件资源：确保数据库服务器具有足够的内存、处理器和磁盘空间，以满足数据库负载的需求。2 优化查询语句：检查并优…
阅读更多...
解析代理IP在跨境电商和社媒营销中的关键作用

解析代理IP在跨境电商和社媒营销中的关键作用

跨境电商和社媒营销领域的从业者深知，代理IP的价值愈发凸显。在推广营销的过程中，频繁遇到因IP关联而封禁账号的情况，或因使用不安全IP而导致异常问题。 这些问题促使人们开始高度重视代理IP的作用。但实际上，代理IP究竟是何物&a…
阅读更多...
Java匿名内部类

Java匿名内部类

文章目录 前言一、使用匿名内部类需要注意什么？二、使用步骤匿名内部类的结构匿名内部类的实用场景1. 事件监听器2. 过滤器3. 线程4. 实现接口5.单元测试：6.GUI编程7.回调函数 前言 Java中的匿名内部类是一种可以在声明时直接创建对象的内部类。这种内部…
阅读更多...
使用Python爬虫采集网络热点

使用Python爬虫采集网络热点

在当今信息爆炸的时代，了解网络热搜词和热点事件对于我们保持时事敏感性和把握舆论动向非常重要。在本文中，我将与你分享使用Python爬虫采集网络热搜词和热点事件的方法，帮助你及时获取热门话题和热点新闻。 1. 网络热搜词采集 网络热搜词是人…
阅读更多...
邂逅JavaScript

邂逅JavaScript

前言：前端三大核心 前端开发最主要需要掌握的是三个知识点：HTML、CSS、JavaScript 一、认识编程语言 1.计算机语言 前面我们已经学习了HTML和CSS很多相关的知识: 在之前我们提到过, HTML是一种标记语言, CSS也是一种样式语言; 他们本身都是属于计算…
阅读更多...
前端基础（Element、vxe-table组件库的使用）

前端基础（Element、vxe-table组件库的使用）

前言：在前端项目中，实际上，会用到组件库里的很多组件，本博客主要介绍Element、vxe-table这两个组件如何使用。 目录 Element 引入element 使用组件的步骤 使用对话框的示例代码 效果展示 vxe-table 引入vxe-table 成果展…
阅读更多...
力扣：73. 矩阵置零（Python3）

力扣：73. 矩阵置零（Python3）

题目： 给定一个 m x n 的矩阵，如果一个元素为 0 ，则将其所在行和列的所有元素都设为 0 。请使用 原地 算法。 来源：力扣（LeetCode） 链接：力扣（LeetCode）官网 - 全球极客挚…
阅读更多...
Java课题笔记~ 综合案例

Java课题笔记~ 综合案例

3.综合案例 3.1 功能介绍 以上是我们在综合案例要实现的功能。除了对数据的增删改查功能外，还有一些复杂的功能，如 批量删除、分页查询、条件查询 等功能 批量删除 功能：每条数据前都有复选框，当我选中多条数据并点击 批量删除 按…
阅读更多...
最新文章

Copyright @ 2022~2023