mysql分组取出每组时间最新的一条数据

• 假设一张表中存放了每个人、不同时间的最新步数（step_count）

#.测试数据（注意：由于程序bug，同一天最新时间采集到的步数不一定最多）
drop table if exists xxx;
create table xxx (id bigint(21), name varchar(64), collect_time datetime, step_count int(11));
insert into xxx values(1,'sam','2025-04-10 08:11:00',3000);
insert into xxx values(2,'sam','2025-04-10 09:12:00',4000);
insert into xxx values(3,'joy','2025-04-10 09:13:00',4000);
insert into xxx values(4,'joy','2025-04-10 09:14:00',4600);
insert into xxx values(5,'joy','2025-04-10 10:15:00',3800);
select * from xxx;

• 场景1：假如要取出每个人中最新时间对应的步数，可参考如下：

#.1.先取出每个人最新的采集时间
select name,max(collect_time) as max_collect_time from xxx group by name;

#.2.再根据最新时间找出对应的步数
select a.name, a.step_count
from xxx as a
join ( select name,max(collect_time) as max_start_time from xxx group by name ) as b
on a.name=b.name and a.collect_time=b.max_start_time;

• 操作记录如下：

(root@localhost) [it]> select * from xxx;
+------+------+---------------------+------------+
| id   | name | collect_time        | step_count |
+------+------+---------------------+------------+
|    1 | sam  | 2025-04-10 08:11:00 |       3000 |
|    2 | sam  | 2025-04-10 09:12:00 |       4000 |
|    3 | joy  | 2025-04-10 09:13:00 |       4000 |
|    4 | joy  | 2025-04-10 09:14:00 |       4600 |
|    5 | joy  | 2025-04-10 10:15:00 |       3800 |
+------+------+---------------------+------------+

(root@localhost) [it]> select a.name, a.step_count from xxx as a join ( select name,max(collect_time) as max_start_time from xxx group by name ) as b on a.name=b.name and a.collect_time=b.max_start_time;
+------+------------+
| name | step_count |
+------+------------+
| sam  |       4000 |
| joy  |       3800 |
+------+------------+
2 rows in set (0.00 sec)

• 场景2：假如每个人只保留一条最新的数据，其他数据删除，则参考如下：

delete t1 from xxx t1 
join ( select name,max(collect_time) as max_collect_time from xxx group by name ) t2
on t1.name = t2.name and t1.collect_time <> t2.max_collect_time;

• 效果如下：

(root@localhost) [it]> select * from xxx;
+------+------+---------------------+------------+
| id   | name | collect_time        | step_count |
+------+------+---------------------+------------+
|    2 | sam  | 2025-04-10 09:12:00 |       4000 |
|    5 | joy  | 2025-04-10 10:15:00 |       3800 |
+------+------+---------------------+------------+