Mysql：通过group by 获取最新一条信息, Get Last Record In Each MySQL Group

假设我们有一张表：

id   category_id  post_title
-------------------------
1    1            Title 1
2    1            Title 2
3    1            Title 3
4    2            Title 4
5    2            Title 5
6    3            Title 6

我想要category_id只取一条最新的：

select * from posts group by category_id

或者：

select * from posts group by category_id order by  id desc

但是上面的语句得到的结果是：

id   category_id  post_title
-------------------------
1    1            Title 1
4    2            Title 4
6    3            Title 6

原因在于，group by 总是返回第一次集合。

解决方法，用以下的mysql查询语句即可：

SELECT id, category_id, post_title
FROM posts
WHERE id IN (
    SELECT MAX(id)
    FROM posts
    GROUP BY category_id
);

返回结果

id   category_id  post_title
-------------------------
3    1            Title 3
5    2            Title 5
6    3            Title 6

以此类推，这个方法，也可以用在 datetime 的数据类型上：

SELECT id, category_id, post_title,created_at
FROM posts
WHERE `created_at` IN (
    SELECT MAX(created_at) 
    FROM posts 
    GROUP BY category_id
);

如果发生 ONLY_FULL_GROUP_BY mode  错误，以上的sql语句前面可以追加一个：

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

本文：Mysql：通过group by 获取最新一条信息, Get Last Record In Each MySQL Group