--> 测试数据：[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