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

求一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 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 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 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 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 行受影响)*/ 
 
[解决办法]
select address from tb where address not like '%蜘蛛%' and address not like '%123%' group by address

应该可以
[解决办法]
谢谢大家 我还有个问题 上述的SQL语句适用于ACCESS数据库么？
--------------------------------
用 like 就行了 %改为*

  where address not like '*蜘蛛*' and address not like '*12*'
[解决办法]
或用instr
[解决办法]
instr这样用
  
SQL code
where instr(address ,'蜘蛛')=0 and instr(address ,'12')=0
[解决办法]
access这样用，或用 not like
SQL code
select count(*)from ( select distinct ip  from tableName where instr(address ,'蜘蛛')=0 and instr(address ,'12')=0)
[解决办法]
SQL code
select ip,count(1) as cntfrom tbwhere  charindex('蜘蛛',address)=0 and charindex('12',address)=0 group by ip
[解决办法]
SQL code
select ip from tb where ip not in(select ip from tb where address like '%蜘蛛%' or address like '%123%')
[解决办法]
SELECT COUNT (DISTINCT IP) FROM IP WHERE charindex('蜘蛛',[address])=0 AND  charindex('12',[address])=0 

 

查看更多 下一篇