try this,with #t as
(select 'gao_ps' ss,'192.168.0.1' ip,1 is_reg,1 has_code union all
select 'gao_ps','192.168.0.1',1,1 union all
select 'gao_ps','192.168.1.1',1,1 union all
select 'gao_ps','192.168.1.1',0,1
)
select ss,
count(1) '注册个数',
sum(case when has_code=1 then 1 else 0 end) '弹代码数',
count(distinct ip) '重复IP注册数'
from #t
where is_reg=1
group by ss
/*
ss 注册个数 弹代码数 重复IP注册数
------ ----------- ----------- -----------
gao_ps 3 3 2(1 row(s) affected)
*/
(select 'gao_ps' ss,'192.168.0.1' ip,1 is_reg,1 has_code union all
select 'gao_ps','192.168.0.1',1,1 union all
select 'gao_ps','192.168.1.1',1,1 union all
select 'gao_ps','192.168.1.1',0,1
)
select ss,
count(1) '注册个数',
sum(case when has_code=1 then 1 else 0 end) '弹代码数',
count(distinct ip) '重复IP注册数'
from #t
where is_reg=1
group by ss
/*
ss 注册个数 弹代码数 重复IP注册数
------ ----------- ----------- -----------
gao_ps 3 3 2(1 row(s) affected)
*/
应该是单ip注册个数大于等于2的注册个数总和.二者好像不太一致喔.