MySQL分组后取最大一条数据【最优解】

本文探讨了在MySQL中如何在分组后获取每组的最大数据,通过分析不同SQL查询方法,如分组、排序、关联子查询和NOT EXISTS,强调了正确方法的重要性。在实践中,尽管某些方法能获取正确结果,但性能差异显著,特别是在大规模数据时,关联子查询和使用NOT EXISTS的写法因利用索引而表现更优。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

视频地址

有一个简单而又常见的需求:分组后取每组的最大一条数据

一、表结构

xdx_user

CREATE TABLE `xdx_user` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(255) NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

xdx_notify

CREATE TABLE `xdx_notify` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `user_id` int(10) NOT NULL COMMENT '用户id',
  `message` varchar(255) NOT NULL COMMENT '消息',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='通知表';

数据插入

INSERT INTO xdx_user (name) values ('张三'),('李四'),('王五');

INSERT into xdx_notify (user_id, message) VALUES 
(1,  '张三消息1'),(1, '张三消息2'),(1,'张三消息3'), (1, "张三消息 max"),
(2,  '李四消息max'),
(3, '王五消息1'),( 3, '王五消息2'),( 3, '王五消息3'),(3, '王五消息4'),(3, '王五消息max');

二、SQL

要求查出每个用户的 id,姓名,最后一次接收的消息(也就是id最大的)

1 张三 张三消息 max
2 李四 李四消息max
3 王五 王五消息max

2-1、分组找出每个用户的消息最大值

SELECT 
	xu.id '用户ID', 
	xu.name '用户名',
	xn.message '消息内容'
FROM xdx_user xu
LEFT JOIN (
	SELECT * FROM xdx_notify 
	WHERE id IN (SELECT MAX(id) FROM xdx_notify GROUP BY user_id)
) xn ON xu.id = xn.user_id

2-2、先排序,再分组 (错误写法)

以前总以为这种办法是可以的,以为会按照分组之后取第一条,今天却发现不行,不知道是不是因为SQL版本的问题

SELECT 
	xu.id '用户ID', 
	xu.name '用户名',
	xn.message '消息内容'
FROM xdx_user xu
LEFT JOIN (
	SELECT * 
	FROM (SELECT * FROM xdx_notify ORDER BY id DESC) 
	tmp GROUP BY tmp.user_id
) xn ON xu.id = xn.user_id

2-3、关联子查询

SELECT 
	xu.id '用户ID', 
	xu.name '用户名',
	xn.message '消息内容'
FROM xdx_user xu
LEFT JOIN (
	SELECT * FROM xdx_notify a
	WHERE id = (SELECT MAX(id) FROM xdx_notify b WHERE a.user_id =  b.user_id)
) xn ON xu.id = xn.user_id

2-4、使用 NOT EXISTS

SELECT 
	xu.id '用户ID', 
	xu.name '用户名',
	xn.message '消息内容'	
FROM xdx_user xu
LEFT JOIN (
	SELECT * 
	FROM xdx_notify a 
	WHERE NOT EXISTS (SELECT 1 FROM xdx_notify b WHERE a.user_id = b.user_id AND a.id < b.id)
) xn ON xu.id = xn.user_id

三、SQL分析

3-1、分组找出每个用户的消息最大值

通过执行结果发现,三次查询都没有走索引,都是全表扫描的
在这里插入图片描述

3-2、关联子查询

和上述结果一样,也是不走索引的
在这里插入图片描述

3-3、使用 NOT EXISTS

仔细看最里面的子查询,是走了主键索引的
在这里插入图片描述

3-4、总结

虽然上面的三种方法都可以拿到正确的结果,看似好像也差不多,但在实际的过程中却有很大的差别。

在主表900w数据,从表400w数据的时候,1、2写法几乎无法运行,但是写法3却可以很快的得出结果,最慢的就是最内层SQL执行比较慢,写法3却是走索引的所以要快很多。

先自我介绍一下,小编13年上师交大毕业,曾经在小公司待过,去过华为OPPO等大厂,18年进入阿里,直到现在。深知大多数初中级java工程师,想要升技能,往往是需要自己摸索成长或是报班学习,但对于培训机构动则近万元的学费,着实压力不小。自己不成体系的自学效率很低又漫长,而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家,初衷也很简单,就是希望帮助到想自学又不知道该从何学起的朋友,同时减轻大家的负担。添加下方名片,即可获取全套学习资料哦

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值