×

mysql group by 组内排序

天外来信 天外来信 发表于2021-04-28 09:37:29 浏览1652 评论0

抢沙发发表评论

newsID是新闻ID,每条新闻有多条评论comment,theTime是发表评论的时间


现在想要查看每条新闻的最新一条评论:


select * from comments group by newsID 显然不行

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


组内排序:

(1)

select 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)

评论列表

访客