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

最新推荐文章于 2024-07-22 02:59:37 发布

小道仙97

最新推荐文章于 2024-07-22 02:59:37 发布

阅读量1.8w

点赞数 5

分类专栏： # MySql 文章标签： mysql 数据库 database 分组查询 GROUP BY

本文链接：https://blog.csdn.net/Tomwildboar/article/details/124497320

版权

MySql 专栏收录该内容

45 篇文章 10 订阅

订阅专栏

本文探讨了如何使用SQL从用户表和通知表中查询每个用户的最新消息。通过四种不同的方法，包括分组查找最大值、错误的排序分组、关联子查询和使用NOT EXISTS，展示了实现这一目标的不同途径。尽管所有方法都能得到正确结果，但在性能上存在显著差异，尤其是在大数据量下，关联子查询和NOT EXISTS方法表现更优。

摘要由CSDN通过智能技术生成

视频地址

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

一、表结构

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