有两张表:
1、新闻表(News)
字段:Nid,Title,AddTime2、新闻评论表(NewsComment)
字段:Cid,NewsID,Conent,UpdateTime求一SQL查询语句,查询7天之内评论最多的新闻,并按评论数多少降次排序。谢
1、新闻表(News)
字段:Nid,Title,AddTime2、新闻评论表(NewsComment)
字段:Cid,NewsID,Conent,UpdateTime求一SQL查询语句,查询7天之内评论最多的新闻,并按评论数多少降次排序。谢
FROM NEWS T1
INNER JOIN (
SELECT NEWSID,COUNT(1) 'COMS'
FROM NEWSCOMMENT
WHERE DATEDIFF(DAY,UPDATETIME,GETDATE())<=7
) T2 ON T1.NEWSID=T2.NEWSID
ORDER BY T2.COMS DESC
--楼主的问题 是有问题的,如果只查询最多的,那就是一条,那排序就无从谈起了
select a.nid,a.title,count(1) as comment_count
from news a inner join newscomment b on a.nid = b.newsid
where datediff(day,updatetime,getdaet())< 8
group by a.nid,a.title
order by comment_count desc