有数据表 comments
------------------------------------------------
| id | newsID | comment | theTime |
------------------------------------------------
| 1 | 1 | aaa | 11 |
------------------------------------------------
| 2 | 1 | bbb | 12 |
------------------------------------------------
| 3 | 2 | ccc | 12 |
selet tt.id,tt.newsID,tt.comment,tt.theTime from(
select id,newsID,comment,theTime from comments order by theTime desc) as tt group by newsID
(2)
select id,newsID,comment,theTime from comments as tt group by id,newsID,comment,theTime having
------------------------------------------------
| id | newsID | comment | theTime |
------------------------------------------------
| 1 | 1 | aaa | 11 |
------------------------------------------------
| 2 | 1 | bbb | 12 |
------------------------------------------------
| 3 | 2 | ccc | 12 |
------------------------------------------------
newsID是新闻ID,每条新闻有多条评论comment,theTime是发表评论的时间
现在想要查看每条新闻的最新一条评论:
select * from comments group by newsID 显然不行
下面有两种方法可以实现:
(1)selet tt.id,tt.newsID,tt.comment,tt.theTime from(
select id,newsID,comment,theTime from comments order by theTime desc) as tt group by newsID
(2)
select id,newsID,comment,theTime from comments as tt group by id,newsID,comment,theTime having
theTime=(select max(theTime) from comments where newsID=tt.newsID)
(3)利用group_concat(comment order by theTime desc)的组内排序功能,不过要截取字符串
原文链接:mysql group by组内排序