SQL2005中怎样判断一组数字是否为连续数？

[yyanzh]

想问问在一张表中有1到999999个数字，怎样可以找出这些连续数字中缺了那个数字？比如像1,2,3,4,5，8,9,11,15 这组连续数字中没有6,7,10,12,13，14，怎样查询出没有的数字：6,7,10,12,13，14

[X_0]

select distinct t1.number
from master..spt_values t1 left join tb t2 on t1.number=t2.id
where t1.number between (select MIN(id) from tb) and (select MAX(id) from tb)
  and t2.id is null

[叶子]

declare @a table (id int)
insert into @a
select 1 union all
select 3 union all
select 5 union all
select 6 union all
select 7 union all
select 10 union all
select 12 union all
select 14 union all
select 16

select distinct b.number from @a 
a right join master..spt_values b on a.id=b.number
where b.number between (select min(id) from @a)
and (select max(id) from @a) and a.id is null

/*
number
-----------
2
4
8
9
11
13
15
*/

[--小F--]

http://topic.csdn.net/u/20090713/11/0f4e30d9-2a93-4e4b-900e-ab2734803e3d.html?8463

[yueaiwo]

scl