|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语句要如何来改进呢

谢谢
...全文
125 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用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
C# IP地址输入控件
C#中的"C# IP地址输入控件"就是为了解决这个问题而设计的，它提供了一个专用于输入IP地址的控件，提高了用户体验和开发效率。这个控件弥补了标准MaskedTextBox在显示和验证IP地址时的功能限制。 首先，让我们来看看...
罗列IP地址的一个应用
罗列IP地址的一个应用（VC）
配置好的listener.ora和tnsnames.ora
在这个文件中，每个条目代表一个服务名，包含了数据库的连接详细信息，例如： 1. `SERVICE_NAME`或`SID`：指明要连接的数据库实例的名称或ID。 2. `DESCRIPTION`：包含了连接描述，可以有多个，比如通过TCP/IP或...
c++ primer第五版第6章答案
5. **内存分配差异**：当形参和实参不是指针类型时，形参会在栈上分配一个新的内存单元来存储实参的副本；若是指针，则实际上传递的是实参的地址。 ### 知识点二：函数调用常见错误 #### 示例 1. **返回类型错误**...
Spring4.0.2Hibernate4.3Freemarker2.3.20 bootstrap整合例子
Spring4 SpringMVC4 Hibernate4 Freemarker Bootstrap3 整合的小DEMO，主要就是一个登录页面，加一个主页面，然后实现增删改查，还有分页的功能 说明：在项目的doc下有数据库文件，新建数据库名为 db_shf，编码设为...
MS-SQL Server

34,838

社区成员

254,632

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章