Sql Server间断与孤岛

创建测试表

if OBJECT_ID('tempdb..#test') is not null
  drop table #test
create table dbo.#test
(
seqval int not null constraint pk_#seqval primary key
);
insert into dbo.#test(seqval)values
(2),(3),(11),(12)--,(12)
,(13),(27),(33),(34),(35),(42);

查询间断

select bb.seqval+1 'start', 
  (select MIN(cc.seqval)-1 from #test cc where cc.seqval>bb.seqval) 'end'
from #test bb
where not exists
(
select * from #test cc
where bb.seqval+1=cc.seqval
)
and bb.seqval<(select MAX(seqval) from #test)

查询结果：

 

 查询孤岛

;with startCte as
(
select ROW_NUMBER() over(order by bb.seqval)'rn',bb.seqval 'startPoint'
from #test bb
where not exists
(select * from #test cc where bb.seqval-1=cc.seqval)
)
,endCte as
(
select ROW_NUMBER() over(order by bb.seqval)'rn',bb.seqval 'endPoint'
from #test bb
where not exists
(select * from #test cc where bb.seqval+1=cc.seqval)
)
select s.startPoint,e.endPoint from startCte s 
  inner join endCte e on s.rn=e.rn

运行结果：