求助T-SQL查询语句排序

悬赏园豆：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

这都试过了

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

T-SQL 数据库 SQL Server

MR.豆耐特 | 菜鸟二级 | 园豆：256
提问于：2012-05-14 22:04

< >

所有回答(5)

楼上正解！

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

用一个变量代替counts试试。

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

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

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

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

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

sql语句可以参考xu_happy_you 。

lucika.zh | 园豆：54 (初学一级) | 2012-05-30 16:21

清除回答草稿

您需要登录以后才能回答，未注册用户请先注册。

欢迎，请先 登录 或者 注册 。

求助T-SQL查询语句排序

欢迎，请先登录或者注册。