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

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
se