求一sql语句,求一共有多少个IP,该怎么处理

求一sql语句,求一共有多少个IP
表如下->

XML code


ip                           address
192.168.1.1                  aaa                          
192.168.1.2                  蜘蛛A
192.168.1.2                  蜘蛛A
192.168.1.2                  蜘蛛A
192.168.1.3                  蜘蛛B
192.168.1.3                  蜘蛛B
192.168.1.4                  www.123.com
192.168.1.4                  www.123.com
192.168.1.5                  www.abc.com
192.168.1.6                  www.ff.com
192.168.1.6                  www.abc.com

如上表所示..
我有个条件，address列里面出现"蜘蛛"和"12"字样的，都不应该计入总数
输出的个数应该为：3
也就是->192.168.1.1 192.168.1.5 192.168.1.6 共三个

PS：应该用group by 和like吧好像...

------解决方案--------------------

SQL code

select count(distinct ip) from tb where address not like '%蜘蛛%' or address not like '%12%'

------解决方案--------------------
select ip,sum(case when charindex('蜘蛛',address)=0 or charindex('12',address)=0 then 0 else 1 end) as cnt
from tb
group by ip

------解决方案--------------------

SQL code

select distinct ip from tablename 
where charindex('蜘蛛',address)<0 and charindex('12',address)<0

------解决方案--------------------

探讨
select ip,sum(case when charindex('蜘蛛',address)=0 or charindex('12',address)=0 then 0 else 1 end) as cnt
from tb
group by ip

------解决方案--------------------

探讨
引用:
select ip,sum(case when charindex('蜘蛛',address)=0 or charindex('12',address)=0 then 0 else 1 end) as cnt
from tb
group by ip

如果需要IP和对应的数量的话应该这样

------解决方案--------------------

SQL code



create table tb(ip varchar(20),address varchar(50))
insert into tb
select 
'192.168.1.1'   ,               'aaa' 
union all select                          
'192.168.1.2'    ,              '蜘蛛A'
union all select  
'192.168.1.2'     ,             '蜘蛛A'
union all select  
'192.168.1.2'    ,              '蜘蛛A'
union all select  
'192.168.1.3'   ,               '蜘蛛B'
union all select  
'192.168.1.3'   ,               '蜘蛛B'
union all select  
'192.168.1.4'   ,               'www.123.com'
union all select  
'192.168.1.4'  ,                'www.123.com'
union all select  
'192.168.1.5'  ,                'www.abc.com'
union all select  
'192.168.1.6'  ,                'www.ff.com'
union all select  
'192.168.1.6'  ,                'www.abc.com'

select * from tb

select count(distinct ip) from tb 
where address not like '%蜘蛛%' and  address not like '%12%'
/*
3
*/
select ip from tb 
where address not like '%蜘蛛%' and  address not like '%12%'
group by ip
/*
192.168.1.1
192.168.1.5
192.168.1.6
*/

------解决方案--------------------
上面的 条件 or 改为 and  

用 or 是错的

------解决方案--------------------
用charindex  或 patindex 或 like 都可以

SQL code


select count(distinct IP) from tableName where patindex('%12%',address)=0 and  patindex('%蜘蛛%',address)=0

------解决方案--------------------

SQL code


if object_id('tb') is not null
   drop table tb
go
create table tb
(
 ip varchar(20),
 address varchar(20)
)
go
insert into tb
select '192.168.1.1','aaa' union all
select '192.168.1.2','蜘蛛A' union all
select '192.168.1.2','蜘蛛A' union all
select '192.168.1.2','蜘蛛A' union all
select '192.168.1.3','蜘蛛B' union all
select '192.168.1.3','蜘蛛B' union all
select '192.168.1.4','www.123.com' union all
select '192.168.1.4','www.123.com' union all
select '192.168.1.5','www.abc.com' union all
select '192.168.1.6','www.ff.com' union all
select '192.168.1.6','www.abc.com'
go
select address from tb where address not like '%蜘蛛%' and address not like '%123%' group by address
go
/*
address
--------------------
aaa
www.abc.com
www.ff.com

(3 行受影响)

*/

求一sql语句,求一共有多少个IP,该怎么处理

相关推荐