mysql - 查找 mysql 表中特定 ID 的最后一条记录

Question

我有一个 MYSQL 表，如下所示:

+-----+-------------+----------------------------+
| id  | prev_status | date_modified              |
+-----+-------------+----------------------------+
| 236 | INIT        | 2015-11-13 08:00:53.128730 |
| 236 | REMS        | 2015-11-13 08:00:53.139382 |
| 236 | RET         | 2015-11-13 08:00:53.146587 |
| 236 | PA          | 2015-11-13 08:00:53.150409 |
| 236 | CORR        | 2015-11-13 08:00:53.157473 |
| 236 | PA          | 2015-11-13 08:00:53.165941 |
| 236 | SUB         | 2015-11-13 08:00:53.174236 |
| 236 | FILL        | 2015-11-13 08:00:53.181169 |
| 236 | SHIP        | 2015-11-13 08:00:53.183954 |
| 570 | INIT        | 2015-11-13 08:01:13.320310 |
| 570 | REMS        | 2015-11-13 08:01:13.333153 |
| 570 | RET         | 2015-11-13 08:01:13.340783 |
| 570 | PA          | 2015-11-13 08:01:13.351363 |
| 570 | SUB         | 2015-11-13 08:01:13.360626 |
| 570 | FILL        | 2015-11-13 08:01:13.368099 |
| 570 | SHIP        | 2015-11-13 08:01:13.371222 |
+-----+-------------+----------------------------+

很明显，数据已经按 ID 排序。我知道 limit 1 将给出表中的最后一条记录。但是，我需要的是每个 id 的最后一条记录。在此特殊情况，我需要:

 +-----+-------------+----------------------------+
 | id  | prev_status | date_modified              |
 +-----+-------------+----------------------------+
 | 236 | SHIP        | 2015-11-13 08:00:53.183954 |
 | 570 | SHIP        | 2015-11-13 08:01:13.371222 |
 +-----+-------------+----------------------------+

我该怎么做？

Answer 1

添加date_modified字段后，查询如下

select s.id, s.prev_status, s.date_modified from (select * from tab1 order by date_modified desc) as s group by s.id;