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

原贴:http://topic.csdn.net/u/20071230/22/f4c19d7f-194d-47c9-9840-474ca79c782f.htmlselect 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语句要如何来改进呢谢谢

解决方案 »

  1.   

    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
    GOdeclare @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
      

  2.   

    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 = 最小的值的那一行
    那这样要怎么来查询呢
      

  3.   

    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','湖北省武汉市 长城宽带')
    godeclare @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.ipscoredrop table tb
    /*
    StartIP                                            EndIP                                              Area
    -------------------------------------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
    220.113.53.0                                       220.113.53.255                                     广东省广州市 东兴小区长城宽带(1 行受影响)
    */
      

  4.   

    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
    GOdeclare @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
      

  5.   

    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秒来查询出来感觉用太多时间了
      

  6.   

    order by startip desc,endip