求一条SQL语句~~~高手帮帮忙~~~

cl1985111

表allip中的记录

id    ip           sid
1     127.0.0.1     a
2     127.0.0.2     a
3     127.0.0.3     a
4     127.0.0.1     a
5     127.0.0.4     b
6     127.0.0.1     a
7     127.0.0.5     c
8     127.0.0.6     d

现在我求sid=a的所有唯一IP数量的总和(就是一个ip只能出现一次)
得到的值应该是3

我用
select count(ip) as c from allip where sid='a' group by ip
得到的答案是每一个ip的数量而不是他们的总和,sql该怎么写呢?求教高人~~~~~~~~~~~~~~~

michealhenry

select count(distinct ip) as c from allip where sid='a'

fanfan1980

select count(distinct ip) as c from allip where sid='a' group by ip

我是杀人狂

select count(ip) as c from allip where sid='a' and ip in(select distinct ip from allip)

雪兽冰牧

--测试数据
create table  t(id int identity(1,1),ip nvarchar(20),sid char(1))
insert t (ip,sid)
select '127.0.0.1','a' union all
select '127.0.0.2','a' union all
select '127.0.0.3','a' union all
select '127.0.0.1','a' union all
select '127.0.0.4','b' union all
select '127.0.0.3','a' union all
select '127.0.0.5','c' union all
select '127.0.0.6','d'
select count(distinct ip) from t where sid = 'a'
drop table t

hiqwolfzzh

select count(distinct ip) from #temp where sid='a'

wgfxman

select count(ip) from (select ip from allip where sid='a' group by ip having count(ip)=1) m

gaoqingfeng

学习一下！

wgfxman

select count(distinct ip) from allip where sid='a'