IP判断问题 select * from 表 where '218.059.018.130' between IpBegin and IpEnd 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 如果你IP的每段都是按0补位的三位,用楼上钻钻的即可.否则参考如下:/*判断IP地址在表中两个IP段的位置(爱新觉罗.毓华 2007-12-31 广东深圳 答csdn zyciis630)原帖地址:http://topic.csdn.net/u/20071230/22/f4c19d7f-194d-47c9-9840-474ca79c782f.html*//*问题描述比我我IP:220.113.49.43 然后我有如下的数据库 那么我如何正确的查询出我的IP是符合哪一个数据呢 StartIP EndIP Area 220.112.208.0 220.112.255.255 湖北省武汉市 长城宽带 220.113.0.0 220.113.48.255 北京市 长城宽带 220.113.49.0 220.113.63.255 广东省广州市 长城宽带 220.113.64.0 220.113.79.22 湖北省武汉市 长城宽带 220.113.79.23 220.113.79.23 湖北省宜昌市 长城宽带刘家大堰小区 220.113.79.24 220.113.81.208 湖北省武汉市 长城宽带 220.113.81.209 220.113.81.209 湖北省武汉市 (汉口)解放大道1511号名仕装饰工程有限公司 220.113.81.210 220.113.107.52 湖北省武汉市 长城宽带 220.113.107.53 220.113.107.53 湖北省武汉市 徐东路逸居苑小区 220.113.107.54 220.113.122.182 湖北省武汉市 长城宽带 220.113.122.183 220.113.122.183 湖北省武汉市 长城宽带湖北大学校内 220.113.122.184 220.113.127.255 湖北省武汉市 长城宽带*/--------------------------------------------------------------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.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.49.43'select startip , endip , area 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)drop table tb/*startip endip area-------------- -------------- --------------------220.113.49.0 220.113.63.255 广东省广州市 长城宽带(1 行受影响)*/----以下用函数实现(csdn happyflystone完成)CREATE FUNCTION dbo.f_IP2Int(@ip char(15))RETURNS bigintASBEGIN 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)ENDGOdeclare @t table(StartIP varchar(15),EndIP varchar(15),Area varchar(100))insert @t select '220.112.208.0','220.112.255.255','湖北省武汉市 长城宽带 'insert @t select '220.113.0.0','220.113.48.255','北京市 长城宽带'insert @t select '220.113.49.0','220.113.63.255','广东省广州市 长城宽带 'insert @t select '220.113.64.0','220.113.79.22','湖北省武汉市 长城宽带' insert @t select '220.113.79.23','220.113.79.23','湖北省宜昌市 长城宽带刘家大堰小区' insert @t select '220.113.79.24','220.113.81.208','湖北省武汉市 长城宽带' insert @t select '220.113.81.209','220.113.81.209','湖北省武汉市 (汉口)解放大道1511号名仕装饰工程有限公司' insert @t select '220.113.81.210','220.113.107.52','湖北省武汉市 长城宽带 'insert @t select '220.113.107.53','220.113.107.53','湖北省武汉市 徐东路逸居苑小区' insert @t select '220.113.107.54','220.113.122.182','湖北省武汉市 长城宽带' insert @t select '220.113.122.183','220.113.122.183','湖北省武汉市 长城宽带湖北大学校内' insert @t select '220.113.122.184','220.113.127.255','湖北省武汉市 长城宽带'declare @ip varchar(15)set @ip = '220.113.49.43'select * from @twhere dbo.f_IP2Int(@ip) between dbo.f_IP2Int(StartIP) and dbo.f_IP2Int(EndIP)drop function f_IP2Int/*StartIP EndIP Area ----------------------------------------------220.113.49.0 220.113.63.255 广东省广州市 长城宽带 (所影响的行数为 1 行)*/---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' ,'广东省广州市 东兴小区长城宽带') 也就是有大区间包含小区间。[code=SQL]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)) bwhere 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 行受影响)*/[/code] 想做一个定时更新的功能,通过SQLSERVERAGENT,请问脚本应该怎么写? 无限级分类问题 Sql Server已经有函数了,为什么还弄出个存储过程? 在企业管理器里无缘无故会跑出一个 颜色是灰色的(有置疑字样的) 错误信息:超时时间已到。在操作完成之前超时时间已过或服务器未响应 求解决方案 求一个最简单的触发器的写法。 Sql语句优化 在存储过程中如何实现类似,MESSAGEBOX的功能?? 高手的地方! 怎样把两个字符型记寻作为日期型比较大小 求一 sql 语句(很常见)急急急急急 sql server 2000数据库急涨,有没有什么办法?
判断IP地址在表中两个IP段的位置
(爱新觉罗.毓华 2007-12-31 广东深圳 答csdn zyciis630)
原帖地址:http://topic.csdn.net/u/20071230/22/f4c19d7f-194d-47c9-9840-474ca79c782f.html
*//*问题描述
比我我IP:220.113.49.43
然后我有如下的数据库
那么我如何正确的查询出我的IP是符合哪一个数据呢
StartIP EndIP Area
220.112.208.0 220.112.255.255 湖北省武汉市 长城宽带
220.113.0.0 220.113.48.255 北京市 长城宽带
220.113.49.0 220.113.63.255 广东省广州市 长城宽带
220.113.64.0 220.113.79.22 湖北省武汉市 长城宽带
220.113.79.23 220.113.79.23 湖北省宜昌市 长城宽带刘家大堰小区
220.113.79.24 220.113.81.208 湖北省武汉市 长城宽带
220.113.81.209 220.113.81.209 湖北省武汉市 (汉口)解放大道1511号名仕装饰工程有限公司
220.113.81.210 220.113.107.52 湖北省武汉市 长城宽带
220.113.107.53 220.113.107.53 湖北省武汉市 徐东路逸居苑小区
220.113.107.54 220.113.122.182 湖北省武汉市 长城宽带
220.113.122.183 220.113.122.183 湖北省武汉市 长城宽带湖北大学校内
220.113.122.184 220.113.127.255 湖北省武汉市 长城宽带
*/--------------------------------------------------------------
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.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.49.43'select startip , endip , area
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)drop table tb/*
startip endip area
-------------- -------------- --------------------
220.113.49.0 220.113.63.255 广东省广州市 长城宽带(1 行受影响)
*/
----以下用函数实现(csdn happyflystone完成)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 @t select '220.112.208.0','220.112.255.255','湖北省武汉市 长城宽带 '
insert @t select '220.113.0.0','220.113.48.255','北京市 长城宽带'
insert @t select '220.113.49.0','220.113.63.255','广东省广州市 长城宽带 '
insert @t select '220.113.64.0','220.113.79.22','湖北省武汉市 长城宽带'
insert @t select '220.113.79.23','220.113.79.23','湖北省宜昌市 长城宽带刘家大堰小区'
insert @t select '220.113.79.24','220.113.81.208','湖北省武汉市 长城宽带'
insert @t select '220.113.81.209','220.113.81.209','湖北省武汉市 (汉口)解放大道1511号名仕装饰工程有限公司'
insert @t select '220.113.81.210','220.113.107.52','湖北省武汉市 长城宽带 '
insert @t select '220.113.107.53','220.113.107.53','湖北省武汉市 徐东路逸居苑小区'
insert @t select '220.113.107.54','220.113.122.182','湖北省武汉市 长城宽带'
insert @t select '220.113.122.183','220.113.122.183','湖北省武汉市 长城宽带湖北大学校内'
insert @t select '220.113.122.184','220.113.127.255','湖北省武汉市 长城宽带'
declare @ip varchar(15)
set @ip = '220.113.49.43'
select *
from @t
where dbo.f_IP2Int(@ip) between dbo.f_IP2Int(StartIP) and dbo.f_IP2Int(EndIP)
drop function f_IP2Int/*
StartIP EndIP Area
----------------------------------------------
220.113.49.0 220.113.63.255 广东省广州市 长城宽带
(所影响的行数为 1 行)
*/
---
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' ,'广东省广州市 东兴小区长城宽带')
也就是有大区间包含小区间。[code=SQL]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.ipscoredrop table tb
/*
StartIP EndIP Area
----------------------------------------------------------
220.113.53.0 220.113.53.255 广东省广州市 东兴小区长城宽带(1 行受影响)
*/[/code]