日期:2014-05-18  浏览次数:20505 次

论坛的排行榜的sql语句
论坛有个帖子表,字段有id(主键)、userid(另外用户表的主键),里面假设数据如下:
1 1
2 1
3 1
4 1
5 2  
6 2  
7 2
8 3
……
如何构建一个sql语句实现根据用户发帖量选取出前10个用户?
求高手指点

------解决方案--------------------
SQL code

--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([id] int,[userid] int)
insert [tbl]
select 2,1 union all
select 3,1 union all
select 4,1 union all
select 5,2 union all
select 6,2 union all
select 7,2 union all
select 8,3 union all
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,1 union all
select 5,2 union all
select 6,2 union all
select 7,2 union all
select 8,3 union all
select 8,3



select top 2 userid,COUNT(1) as times 
from tbl group by userid order by COUNT(1) desc

/*
userid    times
1    7
2    6
*/

------解决方案--------------------

select top 10 userid,username,count(*)
from a join user on s.userid=user.userid
group by userid,uesrname
order by count(*) desc