mysql分组后取最大一条数据【最优解】_mysql分组取每组最大一条-爱代码爱编程
有一个简单而又常见的需求:分组后取每组的最大一条数据
一、表结构
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却是走索引的所以要快很多。