有个查询问题始终不得其解,记得 在查询中有一个distinct选项可以去除重复值表中
name ip
a 127.0.0.1
a 127.0.0.1
a 192.168.0.1
b 192.168.0.1
c 192.168.0.2
c 192.168.0.2
d 192.168.0.3
select distinct name ip from tb_statistic 可以得到name ip
a 127.0.0.1
a 192.168.0.1
b 192.168.0.1
c 192.168.0.2
d 192.168.0.3如果我想统计每个名字的IP个数应该怎么写呢?比如统计结果应该是
name ipCount
a 2 (192.168.0.1,127.0.0.1两个)
b 1
c 1
d 1select name,count(ip) as ipCount from tb_statistic Group By name
获得的是
name ipCount
a 3
b 1
c 2
d 1如何去掉重复的IP值?
name ip
a 127.0.0.1
a 127.0.0.1
a 192.168.0.1
b 192.168.0.1
c 192.168.0.2
c 192.168.0.2
d 192.168.0.3
select distinct name ip from tb_statistic 可以得到name ip
a 127.0.0.1
a 192.168.0.1
b 192.168.0.1
c 192.168.0.2
d 192.168.0.3如果我想统计每个名字的IP个数应该怎么写呢?比如统计结果应该是
name ipCount
a 2 (192.168.0.1,127.0.0.1两个)
b 1
c 1
d 1select name,count(ip) as ipCount from tb_statistic Group By name
获得的是
name ipCount
a 3
b 1
c 2
d 1如何去掉重复的IP值?
select name,ipcount=count(ipcount) from
(
select name,count(ip) as ipCount from tb_statistic Group By name,ip
)b group by name
select name,ipcount=(distinct ip) from tb_statistic Group By name
drop table tb_statistic
go
create table tb_statistic(name char(2),ip varchar(20))
insert into tb_statistic
select 'a', '127.0.0.1' union all
select 'a', '127.0.0.1' union all
select 'a', '192.168.0.1' union all
select 'b', '192.168.0.1' union all
select 'c', '192.168.0.2' union all
select 'c', '192.168.0.2' union all
select 'd', '192.168.0.1'
select name,count(ip) as count_i from
(
select distinct name,ip from tb_statistic
) a
group by name
--
正确接分
from
(select distinct name,ip from # a group by name,ip)a
group by name
(
name char(10),
ip nvarchar(50)
)insert #IP select 'a','127.0.0.1'
insert #IP select 'a','127.0.0.1'
insert #IP select 'a','192.168.0.1'
insert #IP select 'b','192.168.0.1'
insert #IP select 'c','192.168.0.2'
insert #IP select 'c','192.168.0.2'
insert #IP select 'd','192.168.0.2'
select * from #IP
select distinct [name],count(ip) as ipcount from #ip group by ip,name
insert into tb1 select 'a','127.0.0.1'
union all select 'a','127.0.0.1'
union all select 'a','192.168.0.1'
union all select 'b','192.168.0.1'
union all select 'c','192.168.0.2'
union all select 'c','192.168.0.2'
union all select 'd','192.168.0.3'select name,count(distinct ip) from tb1 group by name----------------------------------
a 2
b 1
c 1
d 1
a 127.0.0.1
a 127.0.0.1
a 192.168.0.1
b 192.168.0.1
c 192.168.0.2
c 192.168.0.2
d 192.168.0.3
----------------------
select name , count(distinct ip) ipCount from tb group by name
insert into tb values('a' , '127.0.0.1')
insert into tb values('a' , '127.0.0.1')
insert into tb values('a' , '192.168.0.1')
insert into tb values('b' , '192.168.0.1')
insert into tb values('c' , '192.168.0.2')
insert into tb values('c' , '192.168.0.2')
insert into tb values('d' , '192.168.0.3')
goselect name , count(distinct ip) ipCount from tb group by namedrop table tb/*
name ipCount
---------- -----------
a 2
b 1
c 1
d 1(所影响的行数为 4 行)
*/
select name , count(distinct ip) ipCount from tb group by name
'a','127.0.0.1' union all select
'a','192.168.0.1' union all select
'b','192.168.0.1' union all select
'c','192.168.0.2' union all select
'c','192.168.0.2' union all select
'd','192.168.0.3'--select * from taselect t.[name],count(t.ip)as counts from
(select distinct [name] as [name],ip from ta )t
group by t.[name]
/*
name counts
_______________
a 2
b 1
c 1
d 1
*/