全部栏目

首页 旅游资讯 线路攻略 景点大全 国内游 境外游 美食特产
您的当前位置：首页正文

mysqlgroupby组内排序

2020-11-09 来源：爱够旅游网

有数据表 comments ------------------------------------------------ | id | newsID | comment | theTime | ------------------------------------------------ | 1 | 1 | aaa | 11 | ------------------------------------------------ | 2 | 1 | bbb | 1


有数据表 comments
------------------------------------------------
| 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 显然不行


select * from comments group by newsID order by theTime desc 是组外排序，也不行


下面有两种方法可以实现：

（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)
显示全文