账号通
    

账号  

密码  

2334

查看

1

回复
主题:也许你会用到的投稿排行SQL查询语句 [收藏主题]  
悠客 当前离线

213

主题

15

广播

7

粉丝
添加关注
级别:二年级

用户积分:996 分
登录次数:401 次
注册时间:2010/11/8
最后登录:2019/10/28
悠客 发表于:2013/6/9 14:48:32   | 显示全部帖子 查看该作者主题 楼主 

首先需要在后台作者管理和来源管理中分别增加作者和来源(作为部门),譬如公司所有的职能部门和通讯员。


部门投稿年排行:

  1. select a.CopyFromName,totalNum from [KS_CopyFrom] a,(select CopyFrom,count(*) as totalNum from [KS_InfoList] where ClassID in(1115,1116,1142) and datediff(year,adddate,getdate())=0 and verify=1 group by CopyFrom)b where a.CopyFromName=b.CopyFrom order by b.totalNum desc,adddate

会员投稿年排行:

  1. select a.AuthorName,totalNum from [KS_Author] a,(select Author,count(*) as totalNum from [KS_InfoList] where ClassID in(1115,1116,1142) and datediff(year,adddate,getdate())=0 and verify=1 group by Author)b where a.AuthorName=b.Author order by b.totalNum desc,AuthorID

会员投稿本月排行:

  1. select a.AuthorName,totalNum from [KS_Author] a,(select Author,count(*) as totalNum from [KS_InfoList] where ClassID in(1115,1116,1142) and datediff(month,adddate,getdate())=0 and verify=1 group by Author)b where a.AuthorName=b.Author order by b.totalNum desc,AuthorID


注:ClassID in(1115,1116,1142)里面我直接调用的需要进行统计的栏目ID(某二级栏目下的几个三级子栏目),如果你只建了二级栏目,可以改为ClassID in([KS:Current:ClassChildID])

 
科讯NET交流群 321062970
  支持(0) | 反对(0) 回到顶部顶端 回到底部底部
<上一主题 | 下一主题 >
Powered By KesionCMS Version X1
厦门科汛软件有限公司 © 2006-2016 页面执行0.07813秒 powered by KesionCMS 9.0