我有一张IP数据库比如startip endip local city
============== ============= =============== ====
211.1.151.128 211.2.209.255 日本 NULL
211.16.252.32 211.17.163.159 日本 NULL
211.160.0.0 211.160.9.22 北京市中电飞华 北京
211.160.15.0 211.160.20.255 北京市中电飞华 北京
211.160.160.0 211.160.165.52 上海市 中电飞华 上海我用的IP是211.160.163.52
用的SQL语句是 select city from ip where '211.160.163.52' between startip and endip
为什么我查来查去这5个结果都是一起出来???
那个错了?请高手帮忙说说!!
============== ============= =============== ====
211.1.151.128 211.2.209.255 日本 NULL
211.16.252.32 211.17.163.159 日本 NULL
211.160.0.0 211.160.9.22 北京市中电飞华 北京
211.160.15.0 211.160.20.255 北京市中电飞华 北京
211.160.160.0 211.160.165.52 上海市 中电飞华 上海我用的IP是211.160.163.52
用的SQL语句是 select city from ip where '211.160.163.52' between startip and endip
为什么我查来查去这5个结果都是一起出来???
那个错了?请高手帮忙说说!!
set @myip = '211.160.163.52'
select * from ip where startip <= @myip and endip >= @myip
declare @t table (startip varchar(20),endip varchar(20),local varchar(20),city varchar(20))
insert into @t
select '211.1.151.128', ' 211.2.209.255', '日本' , NULL
union all select '211.16.252.32' , '211.17.163.159' , '日本' , NULL
union all select '211.160.0.0' , ' 211.160.9.22' , '北京市中电飞华' , '北京'
union all select '211.160.15.0' , ' 211.160.20.255' , '北京市中电飞华' , ' 北京'
union all select '211.160.160.0' , '211.160.165.52' , '上海市 中电飞华', '上海'
select * from @t /*
startip endip local city
---------------------------------------------------------------------------------------------------------
211.1.151.128 211.2.209.255 日本 NULL
211.16.252.32 211.17.163.159 日本 NULL
211.160.0.0 211.160.9.22 北京市中电飞华 北京
211.160.15.0 211.160.20.255 北京市中电飞华 北京
211.160.160.0 211.160.165.52 上海市 中电飞华 上海
*/
create Function StrSite(@Str varchar(2000) ,@Word varchar(20) ,@TimesNo int)
returns int
as
begin
declare @i int,@Times int
declare @WordLen int
set @Times=0
set @i=1
set @Wordlen=len(@Word)
while (@i< Len(@Str)+1 )
begin
if substring(@Str,@i,@Wordlen)=@word
set @times=@times+1
--print '第'+cast(@i as varchar(20)) +'是 : ' +cast(@Times as varchar(20))if @times=@TimesNo
break
set @i=@i+1
end
return (@i)
end
declare @str varchar(20)
set @str='211.160.163.52'
select * from @t
where cast( substring(@str,dbo.StrSite(@str,'.',2)+1,len(@str)-dbo.StrSite(@str,'.',2)) as float)
between
cast( substring(startip,dbo.StrSite(startip,'.',2)+1,len(startip)-dbo.StrSite(startip,'.',2)) as float)
and
cast( substring(endip,dbo.StrSite(endip,'.',2)+1,len(endip)-dbo.StrSite(endip,'.',2)) as float)/*startip endip local city
---------------------------------------------------------------------------------------------------------
211.1.151.128 211.2.209.255 日本 NULL
211.160.160.0 211.160.165.52 上海市 中电飞华 上海
*/
declare @t table (startip varchar(20),endip varchar(20),local varchar(20),city varchar(20))
insert into @t
select '211.1.151.128', ' 211.2.209.255', '日本' , NULL
union all select '211.16.252.32' , '211.17.163.159' , '日本' , NULL
union all select '211.160.0.0' , ' 211.160.9.22' , '北京市中电飞华' , '北京'
union all select '211.160.15.0' , ' 211.160.20.255' , '北京市中电飞华' , ' 北京'
union all select '211.160.160.0' , '211.160.165.52' , '上海市 中电飞华', '上海'
select * from @t /*
startip endip local city
---------------------------------------------------------------------------------------------------------
211.1.151.128 211.2.209.255 日本 NULL
211.16.252.32 211.17.163.159 日本 NULL
211.160.0.0 211.160.9.22 北京市中电飞华 北京
211.160.15.0 211.160.20.255 北京市中电飞华 北京
211.160.160.0 211.160.165.52 上海市 中电飞华 上海
*//*
create Function StrSite(@Str varchar(2000) ,@Word varchar(20) ,@TimesNo int)
returns int
as
begin
declare @i int,@Times int
declare @WordLen int
set @Times=0
set @i=1
set @Wordlen=len(@Word)
while (@i< Len(@Str)+1 )
begin
if substring(@Str,@i,@Wordlen)=@word
set @times=@times+1
--print '第'+cast(@i as varchar(20)) +'是 : ' +cast(@Times as varchar(20))if @times=@TimesNo
break
set @i=@i+1
end
return (@i)
end
*/
declare @str varchar(20)
set @str='211.160.163.52'
select * from @t
where cast( substring(@str,dbo.StrSite(@str,'.',2)+1,len(@str)-dbo.StrSite(@str,'.',2)) as float)
between
cast( substring(startip,dbo.StrSite(startip,'.',2)+1,len(startip)-dbo.StrSite(startip,'.',2)) as float)
and
cast( substring(endip,dbo.StrSite(endip,'.',2)+1,len(endip)-dbo.StrSite(endip,'.',2)) as float) and left(@str,dbo.StrSite(@str,'.',2))=left(@str,dbo.StrSite(startip,'.',2))/*startip endip local city
---------------------------------------------------------------------------------------------------------
211.160.160.0 211.160.165.52 上海市 中电飞华 上海
*/
把你的稍微改一改成
declare @str varchar(20)
set @str='210.82.185.67'
select * from (select * from ip B where @str between startip and endip) t where cast( substring(@str,dbo.StrSite(@str,'.',2)+1,len(@str)-dbo.StrSite(@str,'.',2)) as float) between cast( substring(startip,dbo.StrSite(startip,'.',2)+1,len(startip)-dbo.StrSite(startip,'.',2)) as float) and cast( substring(endip,dbo.StrSite(endip,'.',2)+1,len(endip)-dbo.StrSite(endip,'.',2)) as float) and left(@str,dbo.StrSite(@str,'.',2))=left(@str,dbo.StrSite(startip,'.',2))就可以正常用了~呵呵!!
returns int
as
begin
declare @i int,@Times int
declare @WordLen int
set @Times=0
set @i=1
set @Wordlen=len(@Word)
while (@i< Len(@Str)+1 )
begin
if substring(@Str,@i,@Wordlen)=@word
set @times=@times+1
--print '第'+cast(@i as varchar(20)) +'是 : ' +cast(@Times as varchar(20))if @times=@TimesNo
break
set @i=@i+1
end
return (@i)
end