几种精妙的断号查询方法

USE tempdb;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
  Drop TABLE dbo.T1
GO
Create TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
Insert INTO dbo.T1(col1) VALUES(1);
Insert INTO dbo.T1(col1) VALUES(2);
Insert INTO dbo.T1(col1) VALUES(3);
Insert INTO dbo.T1(col1) VALUES(100);
Insert INTO dbo.T1(col1) VALUES(101);
Insert INTO dbo.T1(col1) VALUES(103);
Insert INTO dbo.T1(col1) VALUES(104);
Insert INTO dbo.T1(col1) VALUES(105);
Insert INTO dbo.T1(col1) VALUES(106);

--定位断号位置、范围的算法

--方法1

Select startnum,
  (Select MIN(col1)-1 FROM T1
  Where col1>startnum) AS endnum
FROM (Select col1+1 AS startnum FROM T1
  Where col1+1 NOT IN (Select col1 FROM T1)
  and col1 <(Select max(col1) FROM T1) ) AS  maxnum
orDER BY startnum

--方法2: 用not exists代替not in 后代码更简洁，查询效率更高

select col1+1 as startnum,
 (select MIN(col1)-1 from T1 where k.col1<col1) as endnum
from T1 k
where not exists(select * from T1 where k.col1+1=col1)
  and col1<(select MAX(col1) from T1)  --防止最大数字的后一个数字出现

--方法3：利用前后2个数字相减的差来判断是否连续，当差大于1则不连续。此方法可用于判断任意等差序列是否连续。

select CUr+1 as stratnum,nxt-1 as endnum
from(
 select col1 as cur,(select MIN(col1) from T1 where k.col1<col1) as nxt
 from T1 k) p
where nxt-cur>1

--再来看看已有范围的查询
--方法1：利用比当前值大的最接近最小间断值的值
select MIN(col1) as startnum, grp as endnum
from(
 select col1,
 (select MIN(col1) from T1 a
  where a.col1>=k.col1 and
  not exists(select * from T1 where a.col1+1=col1 )
 ) as grp
 from T1 k ) z
group by grp

--方法2:利用ROW_NUMBER
select MIN(col1) as startnum,
  max(col1) as endnum
from(
 select col1,
  col1-ROW_NUMBER()over(order by col1) as grp
 from T1 k ) z
group by grp