求連續數據中的斷點(sql為2000) 用游標的不給分

正確的數據:1,2,3,...11,12...  
  後一個=前一個+1 
  開始的數據是1(也就是從1開始的自然數)

先數據出錯可能:2,3,4,5,9,10,11,...

  因為使用SQL2000..所以如ISNUMERIC等函數不能使用

SQL code

create table tb(id int)
insert into tb select 2 union all select 3 union all select 4 union all select 5
union all select 9union all select 10 union all select 13 union all select 14 union all select 20
go
select * from(
select n=(select count(*) from(select distinct id from sysobjects)t1 where id<t2.id)+1 
from (select distinct id from sysobjects)t2
)t3 where not exists(select 1 from tb where id=t3.n)
 and n<(select max(id) from tb)
go
drop table tb
/*
n
-----------
1
6
7
8
11
12
15
16
17
18
19

(11 行受影响)

*/

借助master..spt_values系统表。

SQL code

--假设表tb的col列有这些数据
select  b.col
(select col from tb order by col) a
inner join
(select col from tb order by col) b
on a.col=b.col
where a.col<>(b.col-1) 

断号的查询?

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