科讯cms利用sql标签实现会员投稿排行,总排行,月排行,周排行,日排行

九九副站长

总排行

查询语句：

select top 10 a.userid,a.username,totalNum from ks_user a,(select inputer,count(*) as totalnum from ks_article where verific=1 group by inputer)b where a.username=b.inputer order by b.totalnum desc

循环体：

[loop=10]
< br><strong>第{$AutoID}名</strong> {$Field(username,Text,0,...,0,)}(发表文章:{$Field(totalNum,Num,0,2)}篇)</br>
[/loop]

本月排行

查询语句：

select top 10 a.userid,a.username,totalNum from ks_user a,(select inputer,count(*) as totalnum from ks_article where  month(now())= month(adddate)  and verific=1 group by inputer)b where a.username=b.inputer order by b.totalnum desc

注：mssql请使用date()

循环体：

[loop=10]
< br><strong>第{$AutoID}名</strong> {$Field(username,Text,0,...,0,)}(发表文章:{$Field(totalNum,Num,0,2)}篇)</br>
[/loop]

周排行

查询语句：

select top 10 a.userid,a.username,totalNum from ks_user a,(select inputer,count(*) as totalnum from ks_article where datediff('d',adddate,now())<= 7 and  verific=1 group by inputer)b where a.username=b.inputer order by b.totalnum desc

注：mssql数据库为 datediff(day,adddate,date())<= 7

循环体：

[loop=10]
< br><strong>第{$AutoID}名</strong> {$Field(username,Text,0,...,0,)}(发表文章:{$Field(totalNum,Num,0,2)}篇)</br>
[/loop]

另 最近一月内排行为 datediff(day,adddate,date())<= 30

日排行为 datediff(day,adddate,date())= 1

更多排行榜 大家一起讨论吧