我现在有如下表结构
news表id name content createdate
1 aa aaa 2007-5-1
2 bb bbb 2007-5-2
3 cc ccc 2007-5-3
4 dd ddd 2007-5-4news_stat表newsid viewCount viewDate
1 2 2007-5-6
2 1 2007-5-6
1 3 2007-5-10
3 4 2007-5-10
1 1 2007-6-1
4 1 2007-6-1
*****************************************
想返回的结果如下:
条件:根据访总访问问量从高到低,按时间倒序
******************************************
如下:newsid name content createdate
1 aa aaa 2007-5-1
3 cc ccc 2007-5-3
4 dd ddd 2007-5-4
2 bb bbb 2007-5-2
news表id name content createdate
1 aa aaa 2007-5-1
2 bb bbb 2007-5-2
3 cc ccc 2007-5-3
4 dd ddd 2007-5-4news_stat表newsid viewCount viewDate
1 2 2007-5-6
2 1 2007-5-6
1 3 2007-5-10
3 4 2007-5-10
1 1 2007-6-1
4 1 2007-6-1
*****************************************
想返回的结果如下:
条件:根据访总访问问量从高到低,按时间倒序
******************************************
如下:newsid name content createdate
1 aa aaa 2007-5-1
3 cc ccc 2007-5-3
4 dd ddd 2007-5-4
2 bb bbb 2007-5-2
from news AS A
inner join (select newsid,count(*) AS Cnt from news_stat group by newsid) AS B on A.id=B.newsid
order by B.cnt desc
from news AS A
inner join (select newsid,count(*) AS Cnt from news_stat group by newsid) AS B on A.id=B.newsid
order by B.cnt desc,createdate desc
from news AS A
left join (select newsid,sum(viewCount) AS viewCount from news_stat group by newsid) AS B on A.id=B.newsid
order by isnull(B.viewCount) desc,a.createdate desc
from news AS A
left join (select newsid,sum(viewCount) AS viewCount from news_stat group by newsid) AS B on A.id=B.newsid
order by isnull(B.viewCount,0) desc,a.createdate desc
A.*
From
news A, (Select newsid, Count(newsid) As newsidCount From news_stat Group By newsid) B
Where A.newsid = B.newsid
Order By newsidCount Desc, createdate Desc
inner join (select newsid ,sum(viewCount) viewCounts from news group by newsid )b
on a.id=b.newsid
order by b.viewCounts desc
A.*
From
news A
Order By (Select Count(newsid) From news_stat Where newsid = A.newsid) Desc, createdate Desc
我看看..刚去了个WC..回来就这么多回贴了..太感动了..
谢谢两位.你们没有注意到news_stat表中的newsID每天都会记..结果返回的不正确.to:lt1129(修理地球) 和yrwx001()
谢谢两位.你们两位的结果貌似正确.访问量的排序是正确的.但时间的倒序在访问量相同的情况下不正角.to:Haiwer(海阔天空)
谢谢你..你的结果是正确的..多谢了..
inner join (select newsid ,sum(viewCount) viewCounts from news group by newsid )b
on a.id=b.newsid
order by b.viewCounts desc,a.createdate desc
多谢你..你的两个表名搞混了.换过来是正确的.
你把news_stat中没有记的没有选出来..Haiwer(海阔天空) 返回的结果比你多.是因为他把news表中没有访问的全部按0算的.你们两个结果都对.非常感谢两位和回贴的这些热心的人..