IP段的查询！在线！

[daquzi]

这里有个表，
ID province demo startip1 finiship1
1 吉林 网通 222.160.0.0 222.163.3.255
2 吉林 延边州 网通ADSL 222.163.4.0 222.163.6.73
3 吉林 延边州 安图县白河 222.163.6.74 222.163.6.74
4 吉林 延边州 网通 222.163.6.75 222.163.6.191
5 吉林 延边州 安图县 222.163.6.192 222.163.6.192
6 吉林 延边州 网通 222.163.6.193 222.163.6.255
7 吉林 网通 222.163.7.0 222.163.9.255
8 吉林 网通 222.163.20.0 222.163.31.255
........
........
........
这个是个IP数据库


现在给出IP地址，如222.163.20.3
要检索出startip1到finiship1之间的只要存在的位于这个IP地址之间的地址，这个语句应该怎么写？
实现效果如：
查询：222.163.20.3
结果为：ID：8 地址：吉林 网通

[zhangzs8896]

楼上的正解

[userlogin]

不对吧？不会这么简单吧？

[userlogin]

抄一个函数　getIP
/************************* 如何查IP地址段 ******/
create function getIP(@a varchar(15))
returns varchar(15)
As
begin
declare @s varchar(15)
set @s = ''
while charindex('.',@a) > 0
begin
set @s = @s + right('000' + left(@a,charindex('.',@a)),4)
set @a = right(@a,len(@a)-charindex('.',@a))
end
set @s = @s + right('000' + @a,3)
return @s
end

/*
Select dbo.getIP('202.1.110.2')
---------------
202.001.110.002

（所影响的行数为 1 行）


利用此函数即可：

..... where dbo.getIP('222.163.20.3')
between dbo.getIP(startip1) and dbo.getIP(finiship1)

[zjcxc]

其实楼主的IP存储方式设计得不好,用数字来存储的话,就好查询好多,速度也快.

[zjcxc]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_IP2Int]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_IP2Int]
GO

/*--字符型 IP 地址转换成数字 IP

--邹建 2004.08(引用请保留此信息)--*/

/*--调用示例

select dbo.f_IP2Int('192.168.0.11')
select dbo.f_IP2Int('12.168.0.1')
--*/
create function 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

[zjcxc]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_IP2Int]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_IP2Int]
GO

/*--字符型 IP 地址转换成数字 IP

--邹建 2004.08(引用请保留此信息)--*/

/*--调用示例

select dbo.f_IP2Int('192.168.0.11')
select dbo.f_IP2Int('12.168.0.1')
--*/
create function 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 @tb table (ID int ,province nvarchar(20),demo nvarchar(20),startip1 varchar(30),finiship1 varchar(30))
insert @tb select 1,N'吉林',N'网通' ,'222.160.0.0','222.163.3.255'
union all select 2,N'吉林',N'延边州 网通ADSL' ,'222.163.4.0','222.163.6.73'
union all select 3,N'吉林',N'延边州 安图县白河','222.163.6.74','222.163.6.74'
union all select 4,N'吉林',N'延边州 网通' ,'222.163.6.75','222.163.6.191'
union all select 5,N'吉林',N'延边州 安图县' ,'222.163.6.192','222.163.6.192'
union all select 6,N'吉林',N'延边州 网通' ,'222.163.6.193','222.163.6.255'
union all select 7,N'吉林',N'网通' ,'222.163.7.0','222.163.9.255'
union all select 8,N'吉林',N'网通' ,'222.163.20.0','222.163.31.255'

--调用函数实现查询
select * from @tb
where dbo.f_IP2Int('222.160.11.123') between dbo.f_IP2Int(startip1) and dbo.f_IP2Int(finiship1)

/*--测试结果
ID province demo startip1 finiship1
---- ----------- --------- -------------- -----------------
1 吉林 网通 222.160.0.0 222.163.3.255

（所影响的行数为 1 行）
--*/

[zjcxc]

如果楼主的表中,存储的 startip1 ,finiship1 就按数字来存储的话,查询效率就会很高,因为查询的时候根本不需要现次转换.

[daquzi]

不用存储过程可以么？

[LoveSQL]

declare @tb table (ID int ,province nvarchar(20), demo nvarchar(20), startip1 varchar(30), finiship1 varchar(30))
insert into @tb select
1 ,N'吉林' ,N'网通','222.160.0.0','222.163.3.255' union all select
2 ,N'吉林' ,N'延边州 网通ADSL','222.163.4.0','222.163.6.73' union all select
3 ,N'吉林' ,N'延边州 安图县白河','222.163.6.74','222.163.6.74' union all select
4 ,N'吉林' ,N'延边州 网通','222.163.6.75','222.163.6.191' union all select
5 ,N'吉林' ,N'延边州 安图县','222.163.6.192','222.163.6.192' union all select
6 ,N'吉林' ,N'延边州 网通','222.163.6.193','222.163.6.255' union all select
7 ,N'吉林' ,N'网通','222.163.7.0','222.163.9.255' union all select
8 ,N'吉林' ,N'网通','222.163.20.0','222.163.31.255'


--select * from @tb

--测试
declare @str varchar(30)
set @str='222.163.20.3'

select * from @tb where cast(substring(@str,9,len(@str)-9) as float) between cast(substring(startip1,9,len(startip1)-9) as float) and cast(substring(finiship1,9,len(finiship1)-9) as float)

--结果
ID province demo startip1 finiship1
----------- -------------------- -------------------- ------------------------------ ------------------------------
8 吉林 网通 222.163.20.0 222.163.31.255

(1 row(s) affected)

[viptiger]

declare @a table( [ID] int,
province varchar(20),
demo varchar(20),
startip1 varchar(20),
finiship1 varchar(20))

insert @a
select 1,'吉林','网通','222.160.0.0','222.163.3.255'
union all
select 2,'吉林','延边州 网通ADSL','222.163.4.0','222.163.6.73'
union all
select 3,'吉林','延边州 安图县白河','222.163.6.74','222.163.6.74'
union all
select 4,'吉林','延边州 网通','222.163.6.75','222.163.6.191'
union all
select 5,'吉林','延边州 安图县','222.163.6.192','222.163.6.192'
union all
select 6,'吉林','延边州 网通','222.163.6.193','222.163.6.255'
union all
select 7,'吉林','网通 延边州','222.163.7.0','222.163.9.255'
union all
select 8,'吉林','网通 延边州','222.163.20.0','222.163.31.255'

declare @aint bigint
set @aint = convert(bigint,replace('222.163.20.3','.',''))

select * from @a
where @aint between convert(bigint,replace(startip1,'.','')) and convert(bigint,replace(finiship1,'.',''))
and len('222.163.20.3') = len(startip1)

[viptiger]

结果：


(8 row(s) affected)

ID province demo startip1 finiship1
----------- -------------------- -------------------- -------------------- --------------------
8 吉林 网通 延边州 222.163.20.0 222.163.31.255

(1 row(s) affected)