|zyciis| 如何判断一个IP地址在我数据库中的哪一个IP段中呢谢谢第二贴原贴潇洒老乌龟的方法再改进

zyciis631 2007-12-31 02:28:58

原贴:http://topic.csdn.net/u/20071230/22/f4c19d7f-194d-47c9-9840-474ca79c782f.html



select startip , endip , area 

from tb 

where

   cast(PARSENAME(@ip , 4) as int) >= cast(PARSENAME(startip , 4) as int) and cast(PARSENAME(@ip , 4) as int) <= cast(PARSENAME(endip , 4) as int) and

   cast(PARSENAME(@ip , 3) as int) >= cast(PARSENAME(startip , 3) as int) and cast(PARSENAME(@ip , 3) as int) <= cast(PARSENAME(endip , 3) as int) and

   cast(PARSENAME(@ip , 2) as int) >= cast(PARSENAME(startip , 2) as int) and cast(PARSENAME(@ip , 2) as int) <= cast(PARSENAME(endip , 2) as int) and

   cast(PARSENAME(@ip , 1) as int) >= cast(PARSENAME(startip , 1) as int) and cast(PARSENAME(@ip , 1) as int) <= cast(PARSENAME(endip , 1) as int) 



drop table tb

但是如果有以下情况



insert into tb values('220.112.208.0'  ,'220.112.255.255','湖北省武汉市 长城宽带') 

insert into tb values('220.113.0.0'    ,'220.113.48.255' ,'北京市 长城宽带') 

insert into tb values('220.113.49.0'   ,'220.113.63.255' ,'广东省广州市 长城宽带') 

insert into tb values('220.113.53.0'   ,'220.113.53.255' ,'广东省广州市 东兴小区长城宽带')

然后我现在IP为'220.113.53.221'
那么查询出来就会查出为



insert into tb values('220.113.49.0'   ,'220.113.63.255' ,'广东省广州市 长城宽带') 

insert into tb values('220.113.53.0'   ,'220.113.53.255' ,'广东省广州市 东兴小区长城宽带')

这两条记录而我要查的是符合上面查询语句的算法而更加接近的那一条为



insert into tb values('220.113.53.0'   ,'220.113.53.255' ,'广东省广州市 东兴小区长城宽带')

那上面的SQL语句要如何来改进呢

谢谢

...全文

126 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

-狙击手- 2007-12-31

打赏
举报

order by startip desc,endip

zyciis632 2007-12-31

打赏
举报

RE:潇洒老乌龟
你样查询会出现很大的错误
你的方法为

查出符合IP段的最小相差值
然后再查出表IP段相差值相符的
这样可能会查出很多我想应该是



select a.* 

from tb a, 

(

  select ipscore = min(cast(PARSENAME(endip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(endip , 3) as bigint) * 256 * 256 + cast(PARSENAME(endip , 2) as bigint) * 256 + cast(PARSENAME(endip , 1) as bigint) - 

       cast(PARSENAME(startip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(startip , 3) as bigint) * 256 * 256 + cast(PARSENAME(startip , 2) as bigint) * 256 + cast(PARSENAME(startip , 1) as bigint))

  from tb 

  where

   cast(PARSENAME(@ip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(@ip , 3) as bigint) * 256 * 256 + cast(PARSENAME(@ip , 2) as bigint) * 256 + cast(PARSENAME(@ip , 1) as bigint) >=

   cast(PARSENAME(startip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(startip , 3) as bigint) * 256 * 256 + cast(PARSENAME(startip , 2) as bigint) * 256 + cast(PARSENAME(startip , 1) as bigint) and

   cast(PARSENAME(@ip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(@ip , 3) as bigint) * 256 * 256 + cast(PARSENAME(@ip , 2) as bigint) * 256 + cast(PARSENAME(@ip , 1) as bigint) <=

   cast(PARSENAME(endip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(endip , 3) as bigint) * 256 * 256 + cast(PARSENAME(endip , 2) as bigint) * 256 + cast(PARSENAME(endip , 1) as bigint)

) b

where cast(PARSENAME(a.endip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(a.endip , 3) as bigint) * 256 * 256 + cast(PARSENAME(a.endip , 2) as bigint) * 256 + cast(PARSENAME(a.endip , 1) as bigint) - 

      cast(PARSENAME(a.startip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(a.startip , 3) as bigint) * 256 * 256 + cast(PARSENAME(a.startip , 2) as bigint) * 256 + cast(PARSENAME(a.startip , 1) as bigint)

      = b.ipscore And (cast(PARSENAME(@ip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(@ip , 3) as bigint) * 256 * 256 + cast(PARSENAME(@ip , 2) as bigint) * 256 + cast(PARSENAME(@ip , 1) as bigint) >=

   cast(PARSENAME(startip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(startip , 3) as bigint) * 256 * 256 + cast(PARSENAME(startip , 2) as bigint) * 256 + cast(PARSENAME(startip , 1) as bigint) and

   cast(PARSENAME(@ip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(@ip , 3) as bigint) * 256 * 256 + cast(PARSENAME(@ip , 2) as bigint) * 256 + cast(PARSENAME(@ip , 1) as bigint) <=

   cast(PARSENAME(endip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(endip , 3) as bigint) * 256 * 256 + cast(PARSENAME(endip , 2) as bigint) * 256 + cast(PARSENAME(endip , 1) as bigint)

)

我想应该是这样,但这样对我32W条的全球IP记录要花4秒来查询出来感觉用太多时间了

-狙击手- 2007-12-31

打赏
举报

CREATE FUNCTION dbo.f_IP2Int(

@ip char(15)

)RETURNS bigint

AS

BEGIN

    DECLARE @re bigint

    SET @re=0

    SELECT @re=@re+LEFT(@ip,CHARINDEX('.',@ip+'.')-1)*ID

        ,@ip=STUFF(@ip,1,CHARINDEX('.',@ip+'.'),'')

    FROM(

        SELECT ID=CAST(16777216 as bigint)

        UNION ALL SELECT 65536

        UNION ALL SELECT 256

        UNION ALL SELECT 1)a

    RETURN(@re)

END

GO



declare @t table(StartIP varchar(15),EndIP varchar(15),Area varchar(100))





insert into @t values('220.112.208.0'  ,'220.112.255.255','湖北省武汉市 长城宽带') 

insert into @t values('220.113.0.0'    ,'220.113.48.255' ,'北京市 长城宽带') 

insert into @t values('220.113.49.0'   ,'220.113.63.255' ,'广东省广州市 长城宽带') 

insert into @t values('220.113.49.0'   ,'220.113.53.255' ,'广东省广州市 东兴小区长城宽带')





declare @ip varchar(15)

set @ip = '220.113.53.221'

select top 1 * 

from @t

where dbo.f_IP2Int(@ip) between dbo.f_IP2Int(StartIP) and dbo.f_IP2Int(EndIP)

order by startip desc,endip

/*

StartIP         EndIP           Area                                                                                                 

--------------- --------------- ---------------------------------------------------------------------------------------------------- 

220.113.53.0    220.113.53.255  广东省广州市 东兴小区长城宽带



（所影响的行数为 1 行）

*/

select top 1 startip , endip , area 

from @t 

where

   cast(PARSENAME(@ip , 4) as int) >= cast(PARSENAME(startip , 4) as int) and cast(PARSENAME(@ip , 4) as int) <= cast(PARSENAME(endip , 4) as int) and

   cast(PARSENAME(@ip , 3) as int) >= cast(PARSENAME(startip , 3) as int) and cast(PARSENAME(@ip , 3) as int) <= cast(PARSENAME(endip , 3) as int) and

   cast(PARSENAME(@ip , 2) as int) >= cast(PARSENAME(startip , 2) as int) and cast(PARSENAME(@ip , 2) as int) <= cast(PARSENAME(endip , 2) as int) and

   cast(PARSENAME(@ip , 1) as int) >= cast(PARSENAME(startip , 1) as int) and cast(PARSENAME(@ip , 1) as int) <= cast(PARSENAME(endip , 1) as int) 

order by startip desc,endip

/*

StartIP         EndIP           Area                                                                                                 

--------------- --------------- ---------------------------------------------------------------------------------------------------- 

220.113.53.0    220.113.53.255  广东省广州市 东兴小区长城宽带



（所影响的行数为 1 行）

*/



drop function f_IP2Int

-狙击手- 2007-12-31

打赏
举报

再endip倒素

dawugui 2007-12-31

打赏
举报

将第一查询出来的值取最小的IP差,作为一个子表,然后联合原表取结果.

dawugui 2007-12-31

打赏
举报

create table tb(StartIP varchar(50),EndIP varchar(50),Area varchar(100))

insert into tb values('220.112.208.0'  ,'220.112.255.255','湖北省武汉市 长城宽带') 

insert into tb values('220.113.0.0'    ,'220.113.48.255' ,'北京市 长城宽带') 

insert into tb values('220.113.49.0'   ,'220.113.63.255' ,'广东省广州市 长城宽带') 

insert into tb values('220.113.53.0'   ,'220.113.53.255' ,'广东省广州市 东兴小区长城宽带')

insert into tb values('220.113.64.0'   ,'220.113.79.22'  ,'湖北省武汉市 长城宽带') 

insert into tb values('220.113.79.23'  ,'220.113.79.23'  ,'湖北省宜昌市 长城宽带刘家大堰小区') 

insert into tb values('220.113.79.24'  ,'220.113.81.208' ,'湖北省武汉市 长城宽带') 

insert into tb values('220.113.81.209' ,'220.113.81.209' ,'湖北省武汉市 (汉口)解放大道1511号名仕装饰工程有限公司 ')

insert into tb values('220.113.81.210' ,'220.113.107.52' ,'湖北省武汉市 长城宽带') 

insert into tb values('220.113.107.53' ,'220.113.107.53' ,'湖北省武汉市 徐东路逸居苑小区') 

insert into tb values('220.113.107.54' ,'220.113.122.182','湖北省武汉市 长城宽带') 

insert into tb values('220.113.122.183','220.113.122.183','湖北省武汉市 长城宽带湖北大学校内') 

insert into tb values('220.113.122.184','220.113.127.255','湖北省武汉市 长城宽带')

go



declare @ip as varchar(50)

set @ip = '220.113.53.221'





select a.* 

from tb a, 

(

  select ipscore = min(cast(PARSENAME(endip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(endip , 3) as bigint) * 256 * 256 + cast(PARSENAME(endip , 2) as bigint) * 256 + cast(PARSENAME(endip , 1) as bigint) - 

       cast(PARSENAME(startip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(startip , 3) as bigint) * 256 * 256 + cast(PARSENAME(startip , 2) as bigint) * 256 + cast(PARSENAME(startip , 1) as bigint))

  from tb 

  where

   cast(PARSENAME(@ip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(@ip , 3) as bigint) * 256 * 256 + cast(PARSENAME(@ip , 2) as bigint) * 256 + cast(PARSENAME(@ip , 1) as bigint) >=

   cast(PARSENAME(startip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(startip , 3) as bigint) * 256 * 256 + cast(PARSENAME(startip , 2) as bigint) * 256 + cast(PARSENAME(startip , 1) as bigint) and

   cast(PARSENAME(@ip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(@ip , 3) as bigint) * 256 * 256 + cast(PARSENAME(@ip , 2) as bigint) * 256 + cast(PARSENAME(@ip , 1) as bigint) <=

   cast(PARSENAME(endip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(endip , 3) as bigint) * 256 * 256 + cast(PARSENAME(endip , 2) as bigint) * 256 + cast(PARSENAME(endip , 1) as bigint)

) b

where cast(PARSENAME(a.endip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(a.endip , 3) as bigint) * 256 * 256 + cast(PARSENAME(a.endip , 2) as bigint) * 256 + cast(PARSENAME(a.endip , 1) as bigint) - 

      cast(PARSENAME(a.startip , 4) as bigint) * 256 * 256 * 256 + cast(PARSENAME(a.startip , 3) as bigint) * 256 * 256 + cast(PARSENAME(a.startip , 2) as bigint) * 256 + cast(PARSENAME(a.startip , 1) as bigint)

      = b.ipscore



drop table tb

/*

StartIP                                            EndIP                                              Area

-------------------------------------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------

220.113.53.0                                       220.113.53.255                                     广东省广州市 东兴小区长城宽带



(1 行受影响)

*/

dawugui 2007-12-31

打赏
举报

看看.

zyciis632 2007-12-31

打赏
举报

RE:无枪狙击手
这种方法不是很好你只是查询出来的时候再涮选出IP开始比较大的
但是如果我有下面的这种情况的话就不行了



insert into @t values('220.112.208.0'  ,'220.112.255.255','湖北省武汉市 长城宽带') 

insert into @t values('220.113.0.0'    ,'220.113.48.255' ,'北京市 长城宽带') 

insert into @t values('220.113.49.0'   ,'220.113.63.255' ,'广东省广州市 长城宽带') 

insert into @t values('220.113.49.0'   ,'220.113.53.255' ,'广东省广州市 东兴小区长城宽带')

这里因为
'220.113.49.0' ,'220.113.63.255'
'220.113.49.0' ,'220.113.53.255'
都是为:220.113.49.0开始的
所以查询出来的时候他不一定查出那条正确的

所以我想查询方法为



select startip , endip , area 

from tb 

where

   cast(PARSENAME(@ip , 4) as int) >= cast(PARSENAME(startip , 4) as int) and cast(PARSENAME(@ip , 4) as int) <= cast(PARSENAME(endip , 4) as int) and

   cast(PARSENAME(@ip , 3) as int) >= cast(PARSENAME(startip , 3) as int) and cast(PARSENAME(@ip , 3) as int) <= cast(PARSENAME(endip , 3) as int) and

   cast(PARSENAME(@ip , 2) as int) >= cast(PARSENAME(startip , 2) as int) and cast(PARSENAME(@ip , 2) as int) <= cast(PARSENAME(endip , 2) as int) and

   cast(PARSENAME(@ip , 1) as int) >= cast(PARSENAME(startip , 1) as int) and cast(PARSENAME(@ip , 1) as int) <= cast(PARSENAME(endip , 1) as int) 



drop table tb

这个查询出来
再取里面 endip - startip = 最小的值的那一行
那这样要怎么来查询呢

-狙击手- 2007-12-31

打赏
举报

CREATE FUNCTION dbo.f_IP2Int(

@ip char(15)

)RETURNS bigint

AS

BEGIN

    DECLARE @re bigint

    SET @re=0

    SELECT @re=@re+LEFT(@ip,CHARINDEX('.',@ip+'.')-1)*ID

        ,@ip=STUFF(@ip,1,CHARINDEX('.',@ip+'.'),'')

    FROM(

        SELECT ID=CAST(16777216 as bigint)

        UNION ALL SELECT 65536

        UNION ALL SELECT 256

        UNION ALL SELECT 1)a

    RETURN(@re)

END

GO



declare @t table(StartIP varchar(15),EndIP varchar(15),Area varchar(100))





insert into @t values('220.112.208.0'  ,'220.112.255.255','湖北省武汉市 长城宽带') 

insert into @t values('220.113.0.0'    ,'220.113.48.255' ,'北京市 长城宽带') 

insert into @t values('220.113.49.0'   ,'220.113.63.255' ,'广东省广州市 长城宽带') 

insert into @t values('220.113.53.0'   ,'220.113.53.255' ,'广东省广州市 东兴小区长城宽带') 



declare @ip varchar(15)

set @ip = '220.113.53.221' 

select top 1 * 

from @t

where dbo.f_IP2Int(@ip) between dbo.f_IP2Int(StartIP) and dbo.f_IP2Int(EndIP)

order by startip desc

/*

StartIP         EndIP           Area                                                                                                 

--------------- --------------- ---------------------------------------------------------------------------------------------------- 

220.113.53.0    220.113.53.255  广东省广州市 东兴小区长城宽带



（所影响的行数为 1 行）

*/

select top 1 startip , endip , area 

from @t 

where

   cast(PARSENAME(@ip , 4) as int) >= cast(PARSENAME(startip , 4) as int) and cast(PARSENAME(@ip , 4) as int) <= cast(PARSENAME(endip , 4) as int) and

   cast(PARSENAME(@ip , 3) as int) >= cast(PARSENAME(startip , 3) as int) and cast(PARSENAME(@ip , 3) as int) <= cast(PARSENAME(endip , 3) as int) and

   cast(PARSENAME(@ip , 2) as int) >= cast(PARSENAME(startip , 2) as int) and cast(PARSENAME(@ip , 2) as int) <= cast(PARSENAME(endip , 2) as int) and

   cast(PARSENAME(@ip , 1) as int) >= cast(PARSENAME(startip , 1) as int) and cast(PARSENAME(@ip , 1) as int) <= cast(PARSENAME(endip , 1) as int) 

order by startip desc

/*

StartIP         EndIP           Area                                                                                                 

--------------- --------------- ---------------------------------------------------------------------------------------------------- 

220.113.53.0    220.113.53.255  广东省广州市 东兴小区长城宽带



（所影响的行数为 1 行）

*/



drop function f_IP2Int