哦 不需要select * from tb where replace(@ip,'.','') between ltrim(ip_start_one)+ltrim(ip_start_two)+ltrim(ip_start_three) and ltrim(ip_end_one)+ltrim(ip_end_two)+ltrim(ip_end_three)
改结构吧 ip1 ip2 intip1 intip2 ISP 58.211.0.1 58.211.1.0 986906625 986906880 电信 .......intip1=256*256*256*58+256*256*211+256*0+1 intip2=256*256*256*58+256*256*211+256*0+1 select ISP from table where intip>=intip1 and intip<=intip2
改结构吧 ip1 ip2 intip1 intip2 ISP 58.211.0.1 58.211.1.0 986906625 986906880 电信 .......intip1=256*256*256*58+256*256*211+256*0+1 intip2=256*256*256*58+256*256*211+256*1+0select ISP from table where intip>=intip1 and intip<=intip2
select top 1 * from tb where replace(@ip,'.','')*1 between ltrim(ip_start_one)+ltrim(ip_start_two)+ltrim(ip_start_three)+ ltrim(ip_start_four) and ltrim(ip_end_one)+ltrim(ip_end_two)+ltrim(ip_end_three)+ltrim(ip_end_four) order by ltrim(ip_start_one)+ltrim(ip_start_two)+ltrim(ip_start_three)+ ltrim(ip_start_four)*1, ltrim(ip_end_one)+ltrim(ip_end_two)+ltrim(ip_end_three)+ltrim(ip_end_four)
改结构吧,效率会提高很多的file data ip1 58.211.0.1 ip2 58.211.1.0 intip1 986906625 intip2 986906880 ISP 电信 .......intip1=256*256*256*58+256*256*211+256*0+1 intip2=256*256*256*58+256*256*211+256*1+0转化 211.136.168.140 为 int intip=256*256*256*211+256*256*136+256*168+140select ISP from table where intip>=intip1 and intip<=intip2
create table iptable(ip_start_one varchar(3),ip_start_two varchar(3),ip_start_three varchar(3),ip_start_four varchar(3), ip_end_one varchar(3),ip_end_two varchar(3),ip_end_three varchar(3),ip_end_four varchar(3),isp nvarchar(15)) insert into iptable select '211','136','150','67','211','136','172','105','移动GPRS' go declare @ip varchar(15) set @ip='211.136.168.140' declare @ip1 varchar(3),@ip2 varchar(3),@ip3 varchar(3),@ip4 varchar(3) set @ip1=left(@ip,charindex('.',@ip)-1) set @ip=right(@ip,len(@ip)-charindex('.',@ip)) set @ip2=left(@ip,charindex('.',@ip)-1) set @ip=right(@ip,len(@ip)-charindex('.',@ip)) set @ip3=left(@ip,charindex('.',@ip)-1) set @ip4=right(@ip,len(@ip)-charindex('.',@ip)) select isp from IPTABLE where 1= (case when @ip1 between ip_start_one and ip_end_one then (case when ip_start_one<ip_end_one then 1 else (case when @ip2 between ip_start_two and ip_end_two then (case when ip_start_two<ip_end_two then 1 else (case when @ip3 between ip_start_three and ip_end_three then (case when ip_start_three<ip_end_three then 1 else (case when @ip4 between ip_start_four and ip_end_four then 1 else 0 end) end) else 0 end) end) else 0 end) end) else 0 end) /* isp --------------- 移动GPRS(1 行受影响)*/ go drop table iptable
*
from
tb
where
replace(@ip,'.','')
between
ltrim(ip_start_one)+ltrim(ip_start_two)+ltrim(ip_start_three)
and
ltrim(ip_end_one)+ltrim(ip_end_two)+ltrim(ip_end_three)
ip1 ip2 intip1 intip2 ISP
58.211.0.1 58.211.1.0 986906625 986906880 电信
.......intip1=256*256*256*58+256*256*211+256*0+1
intip2=256*256*256*58+256*256*211+256*0+1
select ISP from table where intip>=intip1 and intip<=intip2
ip1 ip2 intip1 intip2 ISP
58.211.0.1 58.211.1.0 986906625 986906880 电信
.......intip1=256*256*256*58+256*256*211+256*0+1
intip2=256*256*256*58+256*256*211+256*1+0select ISP from table where intip>=intip1 and intip<=intip2
where replace(@ip,'.','')*1 between
ltrim(ip_start_one)+ltrim(ip_start_two)+ltrim(ip_start_three)+ ltrim(ip_start_four)
and
ltrim(ip_end_one)+ltrim(ip_end_two)+ltrim(ip_end_three)+ltrim(ip_end_four)
order by ltrim(ip_start_one)+ltrim(ip_start_two)+ltrim(ip_start_three)+ ltrim(ip_start_four)*1,
ltrim(ip_end_one)+ltrim(ip_end_two)+ltrim(ip_end_three)+ltrim(ip_end_four)
ip1 58.211.0.1
ip2 58.211.1.0
intip1 986906625
intip2 986906880
ISP 电信
.......intip1=256*256*256*58+256*256*211+256*0+1
intip2=256*256*256*58+256*256*211+256*1+0转化 211.136.168.140 为 int
intip=256*256*256*211+256*256*136+256*168+140select ISP from table where intip>=intip1 and intip<=intip2
211.136.168.142 这个IP应该是属于
211.136.150.67 211.136.172.105 上海市 移动GPRS
211.136.172.107 211.136.173.169 上海市 移动GPRS如果想查到211.136.168.142 是属于上海市 移动GPRS的,就必须要通过两条记录来确认的
ip_end_one varchar(3),ip_end_two varchar(3),ip_end_three varchar(3),ip_end_four varchar(3),isp nvarchar(15))
insert into iptable select '211','136','150','67','211','136','172','105','移动GPRS'
go
declare @ip varchar(15)
set @ip='211.136.168.140'
declare @ip1 varchar(3),@ip2 varchar(3),@ip3 varchar(3),@ip4 varchar(3)
set @ip1=left(@ip,charindex('.',@ip)-1)
set @ip=right(@ip,len(@ip)-charindex('.',@ip))
set @ip2=left(@ip,charindex('.',@ip)-1)
set @ip=right(@ip,len(@ip)-charindex('.',@ip))
set @ip3=left(@ip,charindex('.',@ip)-1)
set @ip4=right(@ip,len(@ip)-charindex('.',@ip))
select isp from IPTABLE
where 1=
(case when @ip1 between ip_start_one and ip_end_one then
(case when ip_start_one<ip_end_one then 1 else
(case when @ip2 between ip_start_two and ip_end_two then
(case when ip_start_two<ip_end_two then 1 else
(case when @ip3 between ip_start_three and ip_end_three then
(case when ip_start_three<ip_end_three then 1 else
(case when @ip4 between ip_start_four and ip_end_four then 1 else 0 end)
end)
else 0 end)
end)
else 0 end)
end)
else 0 end)
/*
isp
---------------
移动GPRS(1 行受影响)*/
go
drop table iptable