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

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

XML code

ip                           address192.168.1.1                  aaa                          192.168.1.2                  蜘蛛A192.168.1.2                  蜘蛛A192.168.1.2                  蜘蛛A192.168.1.3                  蜘蛛B192.168.1.3                  蜘蛛B192.168.1.4                  www.123.com192.168.1.4                  www.123.com192.168.1.5                  www.abc.com192.168.1.6                  www.ff.com192.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 codeselect 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 codecreate table tb(ip varchar(20),address varchar(50))insert into tbselect '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 tbselect 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.1192.168.1.5192.168.1.6*/
------解决方案--------------------
上面的 条件 or 改为 and  

用 or 是错的
------解决方案--------------------
用charindex  或 patindex 或 like 都可以

SQL codeselect count(distinct IP) from tableName where patindex('%12%',address)=0 and  patindex('%蜘蛛%',address)=0
------解决方案--------------------
SQL codeif object_id('tb') is not null   drop table tbgocreate table tb( ip varchar(20), address varchar(20))goinsert into tbselect '192.168.1.1','aaa' union allselect '192.168.1.2','蜘蛛A' union allselect '192.168.1.2','蜘蛛A' union allselect '192.168.1.2','蜘蛛A' union allselect '192.168.1.3','蜘蛛B' union allselect '192.168.1.3','蜘蛛B' union allselect '192.168.1.4','www.123.com' union allselect '192.168.1.4','www.123.com' union allselect '192.168.1.5','www.abc.com' union allselect '192.168.1.6','www.ff.com' union allselect '192.168.1.6','www.abc.com'goselect address from tb where address not like '%蜘蛛%' and address not like '%123%' group by addressgo/*address--------------------aaawww.abc.comwww.ff.com(3 行受影响)*/