有这样的张表结构
我现在有一个IP:211.136.168.140
我想查询它属于哪个ISP，这个查询语名怎么写。。。
------解决方案--------------------
改结构吧，效率会提高很多的
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+140
select ISP from table where intip>=intip1 and intip<=intip2
------解决方案--------------------
- SQL code
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'godeclare @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 IPTABLEwhere 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 行受影响)*/godrop table iptable