34,838
社区成员




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' ,'广东省广州市 东兴小区长城宽带')
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' ,'广东省广州市 东兴小区长城宽带')
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)
)
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
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 行受影响)
*/
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' ,'广东省广州市 东兴小区长城宽带')
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
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