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

[nizhijie1984]

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

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

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

[--小F--]

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

[nizhijie1984]

1樓的代碼...好像有點問題...

原數據:2 3 4 5 9 10 13 14 20
運行結果卻是:8 12 11 6 19 16 15 18 7 1 17

[nizhijie1984]

2樓的哥們說的還不會用
3樓的哥們你也太傷我的心了...連個FROM都沒有就出來了
結果還報錯:
很明顯子句就不能用order by
訊息 1033，層級 15，狀態 1，行 2
除非同時指定了 TOP 子句，否則ORDER BY 子句在檢視表、內嵌函數、衍生資料表、以及子查詢中均為無效。

[nizhijie1984]

接上
而且撈取的資料是一樣的 我這邊是要斷點吖
2 2
3 3
4 4
5 5
9 9
10 10
13 13
14 14
20 20

[liang145]

create table #TestTable
(id int,[name] nvarchar(10))
insert #TestTable
select 2 ,'bv' union all
select 3 ,'asd' union all
select 5 ,'bv' union all
select 6 ,'hgfd' union all
select 7 ,'5423' union all
select 11 ,'rew' union all
select 13 ,'er' union all
select 17 ,'t' union all
select 18 ,'tdf'

select top 9999 identity(int,1,1) as id into #t from sys.objects,sys.columns

select t1.id as '不存在' from #t as t1 
left join #TestTable t2 on t1.id=t2.id
where t2.id is null and t1.id<(select max(id) from #TestTable)

drop table #T

不存在
-----------
1
4
8
9
10
12
14
15
16

(9 row(s) affected)

[cd731107]

create table tb(col int)
insert tb
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 9 union all
select 10 union all
select 13 union all
select 14 union all
select 20

select * from tb
where col<>(select top 1 col from tb order by col)
and col not in
(select b.col from
tb a,tb b
where a.col+1=b.col)

[cd731107]

create table tb(col int)
insert tb 
select 2 union all
select 3  union all
select 4  union all
select 5  union all
select 9  union all
select 10  union all
select 13  union all
select 14  union all
select 20
 
select * from tb 
where col<>(select top 1 col from tb order by col)
and col not in 
(select b.col from 
tb a,tb b
where a.col+1=b.col)

[nizhijie1984]

4-5樓...
我看了上面的帖子...
我的數據:2 3 4 5 9 10 13 14 20
應該的結果:1,6,7,8,11,12,15,16,17,18,19
結果實驗:
確認起點:6,11,15,21
確認終點:8,12,19

[nizhijie1984]

1樓的數據OK...
時間就太慢了... 給分

[nizhijie1984]

豆子 你也少了

[cd731107]

--假设表tb的col列有这些数据
select  a.*,b.*
from 
(select top  100 percent  col from tb order by col) a
inner join
(select top  100 percent  col from tb order by col) b
on a.col+1=b.col



create table tb(col int)
insert tb 
select 2 union all
select 3  union all
select 4  union all
select 5  union all
select 9  union all
select 10  union all
select 13  union all
select 14  union all
select 20
 
select number 
from master..spt_values 
where type='p'
and number>=1
and number<=(select max(col) from tb)
and number not in (select col from tb)

number      
----------- 
1
6
7
8
11
12
15
16
17
18
19

（所影响的行数为 11 行）

[cd731107]

才明白你的意思，原来以为你要的是某个断点，其实你要的是两个断点之间的值

[-晴天]

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 行受影响)

*/

[GoAwayZ]

借助master..spt_values系统表。

[cd731107]

--假设表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) 

[--小F--]

断号的查询?

[nizhijie1984]

楼上的。。真的不好意思
最近比较忙。。。这段时间又被人摆了一道。。。很不爽
确认以后立即给分