MSSQL的经典问题：缺失范围（间断）

上次介绍了MSSQL的现有范围（孤岛），今天来介绍下与之正好相反的一个问题：缺失范围（间断），先来看下数据：

create table dbo.T2

(

	id int primary key

);



insert	into dbo.T2

(

	id

)

values (2),(3),(11),(12),(13),(27),(33),(34),(35),(42);



select	*

from	dbo.T2 with(nolock)

order by id;

这次我们要找的不再是连续的数据范围，而是缺失的数据范围，也就是4-10，14-26，28-32，36-41这四个范围。

以4-10这个缺失范围为例，实际上就是先找到前一个现有范围的最大值3，然后+1就是缺失的开始，再找到下一个现有范围的最小值11，然后-1，就是缺失的结束。那怎么找前一个现有范围，后一个现有范围呢？老办法，先人为添加一列做个标记：

select	row_number() over(order by id) as rowindex,

		*

from	caolei.dbo.t2 with(nolock)

等下，先别忙著往下做，仔细观察下数据，你会发现，id3的序号是2，id11的序号是3，序号相差为1，正好紧挨着，也就是说找到序号为2的记录，再关联找出序号比它大1，也计算序号为3的记录，我们就可以找到3和11这两条记录了，你说怎么找？代码如下：

;with aaa as

(

	select	row_number() over(order by id) as rowindex,

			*

	from	caolei.dbo.t2 with(nolock)

)

select	*

from	aaa as a inner join

		aaa as b on a.rowindex=b.rowindex-1;

接着继续观察数据，我们发现不仅找到了id为3和11的记录，还把id为2和3也找出来了，问题是2和3不是我们需要的，怎么排除呢？对比两条记录可以发现，id为2和3，相差1，id为3和11，相差大于1，那么如果把id相差1的都排除掉，只保留id大于1的，结果会怎样呢？

;with aaa as

(

	select	row_number() over(order by id) as rowindex,

			*

	from	caolei.dbo.t2 with(nolock)

)

select	*

from	aaa as a inner join

		aaa as b on a.rowindex=b.rowindex-1

where	b.id-a.id>1;

看，留下的数据都是我们需要的了，再稍微加工下：

;with aaa as

(

	select	row_number() over(order by id) as rowindex,

			*

	from	caolei.dbo.t2 with(nolock)

)

select	a.id+1 as StartId,

		b.id-1 as EndId

from	aaa as a inner join

		aaa as b on a.rowindex=b.rowindex-1

where	b.id-a.id>1

看，终于找到我们想要的缺失范围（间断）记录了。

思路简单来说，就是人为创建一个排序列，将序号相邻的两列关联起来，看标识列的差值，如果差值等于1，说明标识列也是连续的，差值不等于1，那么就说明标识列的数值出现了不连续，这就是缺失，然后头+1，尾-1，缺失范围就出来了。