您好,欢迎来到赴品旅游。
搜索
您的当前位置:首页mysqlgroupby组内排序

mysqlgroupby组内排序

来源:赴品旅游

有数据表 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)

Copyright © 2019- fupindai.com 版权所有 赣ICP备2024042792号-2

违法及侵权请联系:TEL:199 1889 7713 E-MAIL:2724546146@qq.com

本站由北京市万商天勤律师事务所王兴未律师提供法律服务