首页 新闻 赞助 找找看

求助T-SQL查询语句排序

0
悬赏园豆:10 [待解决问题]
select Id,Title,AddTime,(select count(0)  from Comments where Article.Id=Comments.NewsId) as counts from Article

 

我要"order by counts desc"

 

这句怎么加进去啊?

 

SELECT Article.Id, Article.Title, Article.AddTime, (select count(0) as counts from Comments where Article.Id=Comments.NewsId) AS counts
FROM Article
ORDER BY (select count(0) as counts from Comments where Article.Id=Comments.NewsId) desc

 

select Id,Title,AddTime,(select count(*) from Comments where Article.Id=Comments.NewsId) as counts from Article
order by counts desc

这都试过了

 

 

请有答案的测试后再回,谢谢!

 

MR.豆耐特的主页 MR.豆耐特 | 菜鸟二级 | 园豆:256
提问于:2012-05-14 22:04
< >
分享
所有回答(5)
0

楼上正解!

中国青见 chinandy | 园豆:202 (菜鸟二级) | 2012-05-14 22:50
0

用一个变量代替counts试试。

悟行 | 园豆:12559 (专家六级) | 2012-05-15 09:10
0

SQL执行顺序:

FROM->JOIN->ON->WHERE->GROUP BY ->HAVING->SELECT->ORDER BY
 
select eatc.Id,eatc.Title,eatc.AddTime,(select count(*)  from Comments ecom where eatc.Id=ecom.NewsId) as counts from Article eatc
order by counts desc
edsonwu | 园豆:146 (初学一级) | 2012-05-15 10:03
0
SELECT A.Id,A.Title,A.AddTime,B.Counts
FROM Article A 
LEFT JOIN 
(SELECT ISNULL(Count(1),0) AS Counts,NewsId FROM Comments GROUP BY NewsId) B
ON A.Id=B.NewsId
ORDER BY B.Counts DESC
xu_happy_you | 园豆:222 (菜鸟二级) | 2012-05-15 10:16
0

楼主的sql语句性能较差,正确的方式是使用表连接的方法去实现。

表A是Article,表B需要我们根据Comments去构造。

sql语句可以参考xu_happy_you 。

lucika.zh | 园豆:62 (初学一级) | 2012-05-30 16:21
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册