求一Select语句
表IPScan
字段如下：（starIP和endIP是ip段，即IP起始和终止的范围）
ID starIP endIP addr
1 218.5.3.205 218.5.3.211 福建省福州市
2 4.37.216.0 4.37.219.255 美国
3 4.19.78.0 4.19.78.255 美国
4 4.19.79.0 4.19.79.63 美国
现在where条件是 美国 30条实际需要ip地址
在美国所有ip段中随机生成30条实际ip，如4.19.78.154，4.37.217.88
[解决办法]
declare @a table(ID int, starIP varchar(30), endIP varchar(30), addr varchar(100))
insert @a select 1 , '218.5.3.205 ', '218.5.3.211 ', '福建省福州市 '
union all select 2 , '4.37.216.0 ', '4.37.219.255 ', '美国 '
union all select 3 , '4.19.78.0 ', '4.19.78.255 ', '美国 '
union all select 4 , '4.19.79.0 ', '4.19.79.63 ', '美国 '
select top 255 id=identity(int,1,1) into # from syscolumns
select top 30 NewIP from
(
select *,NewIP=(case when cast(stary as int)+id <=cast(endY as int) then starx+ltrim(cast(starY as int)+id)
else ' ' end)
from # aa
cross join
(
select
starIP,
starX=left(starip,len(starip)-charindex( '. ',reverse(starip))+1),
starY=right(starip,charindex( '. ',reverse(starip))-1),
endIP,
endX=left(endip,len(endip)-charindex( '. ',reverse(endip))+1),
endY=right(endip,charindex( '. ',reverse(endip))-1)
from @a where addr= '美国 ')bb
)cc
where NewIP <> ' ' order by newId()
drop table #
[解决办法]
declare @i int = 1
declare @ipbegin varchar(15),@usedid int
if exists (select top 1 * from table where addr = 'America ' and cast(right(endIP,charindex(reverse(endIP), '. '))as int) - cast(right(startIP,charindex(reverse(startIP), '. '))as int) > = 30) --如果某個IP段滿足有30個IP 則就在這個IP段取30個
select top 1 @usedid = ID,@ipbegin = right(endIP,Len(startIp)-charindex(reverse(endIP), '. ')) from table where addr = 'America ' and cast(right(endIP,charindex(reverse(endIP), '. '))as int) - cast(right(startIP,charindex(reverse(startIP), '. '))as int) > 30
while @i <= 30
begin
select IP = @ipbegin + cast(cast(right(startIP,charindex(reverse(startIP), '. '))as int) + @i as varchar(1)) from table where ID = @usedid
set @i+= 1
end
else --沒有哪個IP段有30個,就選最接近30個的IP段
declare @icount1 int
select top 1 @usedid = ID,@icuont1 = cast(right(endIP,charindex(reverse(endIP), '. '))as int) - cast(right(startIP,charindex(reverse(startIP), '. '))as int) from table where addr = 'America ' order by @icount1
while @i <= @icount1
begin
select IP = @ipbegin + cast(cast(right(startIP,charindex(reverse(startIP), '. '))as int) + @i as varchar(1)) from table where ID = @usedid
set @i+= 1
end
...
再對30 - @icount 判斷 一直下去 直到 30-@icount = 0
