mysql分组查询详解(group by & having)
mhr18 2024-10-09 12:21 22 浏览 0 评论
- 分组查询语法
- 聚合函数
- 单字段分组
- 多字段分组
- 分组前筛选数据
- 分组后筛选数据
- 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多列查询的使用，下去可以试试
相关推荐
- B站收藏视频失效?mybili 收藏夹备份神器完整部署指南
-
本内容来源于@什么值得买APP，观点仅代表作者本人｜作者：羊刀仙很多B站用户都有过类似经历：自己精心收藏的视频突然“消失”，点开一看不是“已被删除”，就是“因UP主设置不可见”。而B站并不会主动通知...
- 中间件推荐初始化配置
-
Redis推荐初始化配置bind0.0.0.0protected-modeyesport6379tcp-backlog511timeout300tcp-keepalive300...
- Redis中缓存穿透问题与解决方法
-
缓存穿透问题概述在Redis作为缓存使用时，缓存穿透是常见问题。正常查询流程是先从Redis缓存获取数据，若有则直接使用；若没有则去数据库查询，查到后存入缓存。但当请求的数据在缓存和数据库中都...
- 后端开发必看!Redis 哨兵机制如何保障系统高可用?
-
你是否曾在项目中遇到过Redis主服务器突然宕机，导致整个业务系统出现数据读取异常、响应延迟甚至服务中断的情况？面对这样的突发状况，作为互联网大厂的后端开发人员，如何快速恢复服务、保障系统的高可用...
- Redis合集-大Key处理建议
-
以下是Redis大Key问题的全流程解决方案，涵盖检测、处理、优化及预防策略，结合代码示例和最佳实践：一、大Key的定义与风险1.大Key判定标准数据类型大Key阈值风险场景S...
- 深入解析跳跃表:Redis里的"老六"数据结构，专治各种不服
-
大家好，我是你们的码农段子手，今天要给大家讲一个Redis世界里最会"跳科目三"的数据结构——跳跃表（SkipList）。这货表面上是个青铜，实际上是个王者，连红黑树见了都要喊声大哥。...
- Redis 中 AOF 持久化技术原理全解析，看完你就懂了!
-
你在使用Redis的过程中，有没有担心过数据丢失的问题？尤其是在服务器突然宕机、意外断电等情况发生时，那些还没来得及持久化的数据，是不是让你夜不能寐？别担心，Redis的AOF持久化技术就是...
- Redis合集-必备的几款运维工具
-
Redis在应用Redis时，经常会面临的运维工作，包括Redis的运行状态监控，数据迁移，主从集群、切片集群的部署和运维。接下来，从这三个方面，介绍一些工具。先来学习下监控Redis实时...
- 别再纠结线程池大小 + 线程数量了，没有固定公式的!
-
我们在百度上能很轻易地搜索到以下线程池设置大小的理论：在一台服务器上我们按照以下设置CPU密集型的程序-核心数+1I/O密集型的程序-核心数*2你不会真的按照这个理论来设置线程池的...
- 网络编程—IO多路复用详解
-
假如你想了解IO多路复用，那本文或许可以帮助你本文的最大目的就是想要把select、epoll在执行过程中干了什么叙述出来，所以具体的代码不会涉及，毕竟不同语言的接口有所区别。基础知识IO多路复用涉及...
- 5分钟学会C/C++多线程编程进程和线程
-
前言对线程有基本的理解简单的C++面向过程编程能力创造单个简单的线程。创造单个带参数的线程。如何等待线程结束。创造多个线程，并使用互斥量来防止资源抢占。会使用之后，直接跳到“汇总”，复制模板来用就行...
- 尽情阅读，技术进阶，详解mmap的原理
-
1.一句话概括mmapmmap的作用，在应用这一层，是让你把文件的某一段，当作内存一样来访问。将文件映射到物理内存，将进程虚拟空间映射到那块内存。这样，进程不仅能像访问内存一样读写文件，多个进程...
- C++11多线程知识点总结
-
一、多线程的基本概念1、进程与线程的区别和联系进程：进程是一个动态的过程，是一个活动的实体。简单来说，一个应用程序的运行就可以被看做是一个进程；线程：是运行中的实际的任务执行者。可以说，进程中包含了多...
- 微服务高可用的2个关键技巧，你一定用得上
-
概述上一篇文章讲了一个朋友公司使用SpringCloud架构遇到问题的一个真实案例，虽然不是什么大的技术问题，但如果对一些东西理解的不深刻，还真会犯一些错误。这篇文章我们来聊聊在微服务架构中，到底如...
- Java线程间如何共享与传递数据
-
1、背景在日常SpringBoot应用或者Java应用开发中，使用多线程编程有很多好处，比如可以同时处理多个任务，提高程序的并发性；可以充分利用计算机的多核处理器，使得程序能够更好地利用计算机的资源，...
欢迎 你 发表评论:
- 一周热门
- 最近发表
- 标签列表
-
- oracle位图索引 (63)
- oracle批量插入数据 (62)
- oracle事务隔离级别 (53)
- oracle 空为0 (50)
- oracle主从同步 (55)
- oracle 乐观锁 (51)
- redis 命令 (78)
- php redis (88)
- redis 存储 (66)
- redis 锁 (69)
- 启动 redis (66)
- redis 时间 (56)
- redis 删除 (67)
- redis内存 (57)
- redis并发 (52)
- redis 主从 (69)
- redis 订阅 (51)
- redis 登录 (54)
- redis 面试 (58)
- 阿里 redis (59)
- redis 搭建 (53)
- redis的缓存 (55)
- lua redis (58)
- redis 连接池 (61)
- redis 限流 (51)