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