首先需要在后台作者管理和来源管理中分别增加作者和来源(作为部门),譬如公司所有的职能部门和通讯员。
部门投稿年排行:
-
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
会员投稿年排行:
-
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
会员投稿本月排行:
-
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])