MySQL分组取最后一条数据

比如，在MySQL数据库中，有数据表messages和数据记录，如下：

mysql> select * from messages;
+----+------+-----------+
| id | name | data      |
+----+------+-----------+
|  1 | A    | A_data_1  |
|  2 | A    | A_data_2  |
|  3 | A    | A_data_3  |
|  4 | B    | B_data_1  |
|  5 | B    | B_data_2 |
|  6 | C    | C_data_1  |
+----+------+-----------+
6 rows in set (0.00 sec)

方法一

select * from messages where id in (select max(id) from messages group by name);

方法二

select m1.* from messages m1 left join messages m2
on m1.name=m2.name and m1.id<m2.id
where m2.id is null;

方法二稍微难理解一点，但是只要把中间结果输出出来，很快就可以理解了，如下：

select * from messages m1 left join messages m2
on m1.name=m2.name and m1.id<m2.id;

结果：

mysql> select * from messages m1 left join messages m2
    -> on m1.name=m2.name and m1.id<m2.id;
+----+------+----------+------+------+----------+
| id | name | data     | id   | name | data     |
+----+------+----------+------+------+----------+
|  1 | A    | A_data_1 |    2 | A    | A_data_2 |
|  1 | A    | A_data_1 |    3 | A    | A_data_3 |
|  2 | A    | A_data_2 |    3 | A    | A_data_3 |
|  3 | A    | A_data_3 | NULL | NULL | NULL     |
|  4 | B    | B_data_1 |    5 | B    | B_data_2 |
|  5 | B    | B_data_2 | NULL | NULL | NULL     |
|  6 | C    | C_data_1 | NULL | NULL | NULL     |
+----+------+----------+------+------+----------+
7 rows in set (0.00 sec)

可以看到id较大的进行左连接的时候，右侧都是NULL，于是就可以找出ID最大的行。