上海地区的IP从纯真数据库中拿出来
59.78.22.69 59.78.22.138
59.78.22.139 59.78.22.139
59.78.22.140 59.78.22.142
59.78.22.143 59.78.22.143
59.78.22.144 59.78.22.255
。。。。。
这样分的很细
但我们公司内部数据库中的上海地区的IP段是
027184000000 027191255255
058024000000 058025255255
058032000000 058034059255
。。。。。
这样的，分的不细。
我想在纯真数据库中查找到上海的IP中找出公司内部上海地区的IP段里没有的IP
因为数据太多，不可能一个一个查。
------解决方案--------------------
- SQL code
--将公司内部数据每隔3位加'.'，去掉最后一个'.'--替换里面的'0'为空，和上海的作比较！
------解决方案--------------------
- SQL code
create table t1(id varchar(100))insert into t1select '58.78.22.69 58.78.22.110' union allselect '59.28.2.169 59.28.2.245' union allselect '158.78.22.9 158.78.22.78' union allselect '169.8.22.69 169.8.22.129' union allselect '192.168.22.69 192.168.22.209' union allselect '127.0.0.1 127.0.0.110'gocreate table t2(id varchar(100))insert into t2select '058078022069 058078022110' union allselect '158078022009 158078022078' union allselect '169008022069 169008022129' union allselect '127000000001 127000000110'gocreate function f_str(@a varchar(100))returns varchar(100)asbegindeclare @id intdeclare @s1 varchar(100)declare @s2 varchar(100)declare @s1g varchar(100)declare @s2g varchar(100)set @id = 1set @s1 = ''set @s2 = ''set @s1g = ''set @s2g = ''set @s1 = substring(@a,1,charindex(' ',@a)-1)set @s2 = substring(@a,charindex(' ',@a)+1,len(@a))while(@id <= 4) begin set @s1g = @s1g +'.'+ ltrim(cast(substring(@s1,1,3) as int)) set @s1 = substring(@s1,4,len(@s1)) set @s2g = @s2g +'.'+ ltrim(cast(substring(@s2,1,3) as int)) set @s2 = substring(@s2,4,len(@s2)) set @id = @id + 1 endset @a = stuff(@s1g,1,1,'')+' '+stuff(@s2g,1,1,'')return @aendgoselect idfrom t1where id not in (select dbo.f_str(id)id from t2)drop function f_strdrop table t1,t2id----------------------------------59.28.2.169 59.28.2.245192.168.22.69 192.168.22.209(2 行受影响)