找出ID不连续的记录,偶没思路了

[wishton_126]

－－测试数据

create table table_test
(
id int ,
name Varchar(50)
)

insert into table_test


select 1,'A'
union all

Select 2,'B'

union all

Select 3,'C'

union all

Select 11,'S'

union all

Select 12,'Q'

union all

Select 15,'W'

union all

Select 17,'Q'

union all

Select 19,'Z'

--Select * from table_test





输出结果为

id name
----------- --------
3 C
11 S
12 Q
15 W
17 Q
19 Z

11， 12 虽然连续， 但 3，11 不连续， 12，15 不连续， 所以 11,12 也要 输出

[zjcxc]

看不明白,3,11不连续怎么又关11,12连续的事

[wishton_126]

我的意思 是 输出 ID不连续的记录

[lsxaa]

select *
from t a
where not exists(select 1 from t where id=a.id-1 and id=a.id+1)

[tinghuyang]

up

[wishton_126]

lsxaa(小李铅笔刀) 兄


select 1


1 是什么意思啊？

[lsxaa]

上面的不对 用这个

select *
from table_test a
where (not exists(select 1 from table_test where id=a.id-1 ) and id<>1)
or not exists(select 1 from table_test where id=a.id+1 )

[lsxaa]

随便可以写成* 和结果没关系的

[samuelpan]

用游标的话很多事情都可以搞定的。

[samuelpan]

select *
from table_test a
where not exists(select 1 from table_test where id=a.id+1)
or not exists(select 1 from table_test where id=a.id-1)
and a.id!=(select min(id) from table_test)
and a.id!=(select max(id) from table_test)

[lsxaa]

samuelpan(欧乐) 的正确

[lsxaa]

这个也行的
select *
from table_test a
where (not exists(select 1 from table_test where id=a.id-1 )
and a.id<>(select min(id) from table_test) )
or ( not exists(select 1 from table_test where id=a.id+1 )
and a.id<>(select max(id) from table_test )