22,209
社区成员
发帖
与我相关
我的任务
分享
--原始数据:@T
declare @T table(服务器地址 varchar(11), 登录用户号 int)
insert @T
select '192.168.0.1',10001 union all
select '192.168.0.1',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10002 union all
select '192.168.0.2',10002 union all
select '192.168.0.3',10002 union all
select '192.168.0.3',null union all
select '192.168.0.3',null union all
select '192.168.0.3',10002
--如果原始数据表有主键或唯一性列,不需要临时表:
select ID=identity(int,1,1),* into #Temp from @T where 登录用户号 is not null
select 登录用户号,有效次数=count(*)
from #Temp a where ID in (select top 5 ID from #Temp where 服务器地址=a.服务器地址 and 登录用户号=a.登录用户号)
group by 登录用户号
order by 有效次数 desc
/*
登录用户号 有效次数
10001 7
10002 4
*/
--删除测试
drop table #Temp
--原始数据:@T
declare @T table(服务器地址 varchar(11), 登录用户号 int)
insert @T
select '192.168.0.1',10001 union all
select '192.168.0.1',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10001 union all
select '192.168.0.2',10002 union all
select '192.168.0.2',10002 union all
select '192.168.0.3',10002 union all
select '192.168.0.3',null union all
select '192.168.0.3',null union all
select '192.168.0.3',10002
select 登录用户号,有效次数=sum(case when IP>5 then 5 else IP end)
from (select 登录用户号,服务器地址,IP=count(1) from @T where 登录用户号 is not null group by 登录用户号,服务器地址) AS a
group by 登录用户号
order by 有效次数 desc
/*
登录用户号 有效次数
10001 7
10002 4
*/
select 排名=identity(int,1,1),登录用户号,有效次数=sum(case when IP>5 then 5 else IP end)
into #Result
from (select 登录用户号,服务器地址,IP=count(1) from @T where 登录用户号 is not null group by 登录用户号,服务器地址) AS a
group by 登录用户号
order by 有效次数 desc
select * from #Result
/*
排名 登录用户号 有效次数
1 10001 7
2 10002 4
*/
--删除测试
drop table #Result
select 登录用户号,有效次数=sum(case when IP>5 then 5 else IP end)
from (select 登录用户号,服务器地址,IP=count(1) from 表 where 登录用户号 is not null group by 登录用户号,服务器地址) AS a
group by 登录用户号
order by 有效次数 desc