SQL查询

Question

--> 测试数据: #a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a (IP varchar(10))
insert into #a
select '110.10.121' union all
select '110.10.122' union all
select '110.10.123' union all
select '110.10.124' union all
select '110.10.125' union all
select '110.10.126' union all
select '110.10.130' union all
select '110.11.213' union all
select '110.11.214' union all
select '110.11.219' union all
select '110.11.248' union all
select '110.11.249'
select id=identity(int,1,1),* into #t1 from #a a 
where not exists(select 1 from #a where left(ip,7)=left(a.ip,7) and right(ip,3)*1=right(a.ip,3)*1+1)
select id=identity(int,1,1),* into #t2 from #a a 
where not exists(select 1 from #a where left(ip,7)=left(a.ip,7) and right(ip,3)*1=right(a.ip,3)*1-1)
select b.ip+'.0-'+a.ip+'.255' as ip from #t1 a,#t2 b where left(b.ip,7)=left(a.ip,7) and a.id=b.id/*ip                          
--------------------------- 
110.10.121.0-110.10.126.255
110.10.130.0-110.10.130.255
110.11.213.0-110.11.214.255
110.11.219.0-110.11.219.255
110.11.248.0-110.11.249.255（所影响的行数为 5 行）

Answer 1

right,left 不能通用
有可能IP位数不一样

Answer 2

declare @tab table(ip nvarchar(100))
insert into @tab
select '110.10.121' union all
select '110.10.122' union all
select '110.10.123' union all
select '110.10.124' union all
select '110.10.125' union all
select '110.10.126' union all
select '110.10.130' union all
select '110.11.213' union all
select '110.11.214' union all
select '110.11.219' union all
select '110.11.248' union all
select '110.11.249';
with MM
as
(
select  ip , ipnew=Cast(reverse(left(reverse(ip),charindex('.',reverse(ip))-1)) as int),iid=ROW_NUMBER()over(order by ip) from @tab 
)
,
MM1
as
(
select ip,case when ipnew-(select max(ipnew) from MM b where b.iid=a.iid-1) is null then 1 else 
ipnew-(select max(ipnew) from MM b where b.iid=a.iid-1) end  as grp ,iid from MM a
)
,
mm2
as 
(
select a.iid,a.ip,grp2=case when grp=1 then 1  when grp<>1 and (select min(grp) from MM1 b where b.iid=a.iid +1)=1 then 1 else grp end
  from  mm1 a
)
,mm3
as
(
select *,grp3=
case when a.grp2=1 then 
(select top(1) ip  from mm2 b where b.iid>a.iid and a.grp2<>b.grp2 order by iid) 
else 
(select max(ip) from mm2 b where b.iid=a.iid+1)
end
from mm2 a
)
select MIN(ip)+'.0',MAX(ip)+'.255' from mm3  group by grp3 order by 1