group by后不能 orderby解决方法(组内排序的方法)详解数据库

select * from comment group by user_id; 
+----+---------+---------+---------------------+---------------------+ 
| id | user_id | content | addtime             | lastmodify          | 
+----+---------+---------+---------------------+---------------------+ 
|  1 |       1 | 评论1   | 2017-05-17 00:00:00 | 2017-05-17 00:00:00 | 
|  3 |       2 | 评论1   | 2017-05-17 00:00:02 | 2017-05-17 00:00:02 | 
|  5 |       3 | 评论1   | 2017-05-17 00:00:04 | 2017-05-17 00:00:04 | 
|  7 |       4 | 评论1   | 2017-05-17 00:00:06 | 2017-05-17 00:00:06 | 
+----+---------+---------+---------------------+---------------------+ 

可以看到结果，分组后只会返回分组内的第一条数据。因为group by语法没有进行组内排序的功能，只会按mysql默认的排序显示。

这种情况我们可以使用id代替时间去搜寻并组内排序，使用max(id)就可以获取到每个分组中最大的评论id(即最新的评论)

 
select * from comment where id in(select max(id) from comment group by user_id) order by user_id; 
+----+---------+---------+---------------------+---------------------+ 
| id | user_id | content | addtime             | lastmodify          | 
+----+---------+---------+---------------------+---------------------+ 
|  6 |       1 | 评论3   | 2017-05-17 00:00:05 | 2017-05-17 00:00:05 | 
|  4 |       2 | 评论2   | 2017-05-17 00:00:03 | 2017-05-17 00:00:03 | 
| 11 |       3 | 评论2   | 2017-05-17 00:00:10 | 2017-05-17 00:00:10 | 
| 10 |       4 | 评论4   | 2017-05-17 00:00:09 | 2017-05-17 00:00:09 | 
+----+---------+---------+---------------------+---------------------+