mysql分组统计每组最新数据

应用场景：

有一张表，记录了每个人发送消息的时间和内容，大致格式如下：

id	user_id	msg	add_time
1	111	hello	2024-01-01 12:00:00
2	222	world	2024-01-01 14:00:00
3	333	ganha	2024-01-04 16:00:00
4	111	aniour	2024-01-11 14:00:00
5	333	kongmawa	2024-01-21 14:00:00

 

 现在需要统计每个人最新的访问内容和时间。有下面3个思路：

1、group by + order by 

order by 按照时间倒序排列：select * from test order by add_time desc;

group by 将它们按用户分组，每组自动获取第一个数据展示。

select * from (select * from test order by add_time desc) a group by user_id;

注意：

在mysql5.7中，系统会把子查询里的order by优化掉，导致不生效。解决方案：加上limt。限制条数尽可能设置得大一些。

 select * from (select * from test order by add_time desc limit 1000) a group by user_id;

 

2、group by + max + left join

通过group by和max()获取每个用过户对应的最新时间：select user_id, max(add_time) from test group by user_id;

将获取到的user_id和max(add_time)作为筛选条件left join到原表：

select a.* from 

(select user_id, max(add_time) as add_time from test group by user_id) b

left join test a 

on b.user_id = a.user_id

and b.add_time = a.add_time;

 

3、自增id + user_id + max + in

适用场景范围：id为自增字段且唯一，那么最新的add_time的数据对应id值最大。

通过group by和max()获取每个用户对应的id最大值：select max(id) from test group by user_id;

通过in查询这些max_ids的记录：

select * from test where id in 

(select max(id) from test group by user_id);