Question[SQL]: How can I list non-contignous data?-优快云博客

本文介绍了一种在SQL中找出非连续数据的方法。通过创建一张包含若干离散ID的测试表，并利用子查询来筛选出那些既没有前驱也没有后继的数据行，以此找到数据中的断点。

Question: How can I list non-contignous data?
In database pubs, I create a table test using statement as below, and I insert several row as below

程序代码
create table test
( id int primary key )
go

insert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (5 )
insert into test values (6 )
insert into test values (8 )
insert into test values (9 )
insert into test values (11)
insert into test values (12)
insert into test values (13)
insert into test values (14)
insert into test values (18)
insert into test values (19)
go

Now I want to list the result of the non-contignous row as below,how can I do it?

程序代码
Missing after Missing before
------------- --------------
6 8
9 11

…

    if(OBJECT_ID('test') is not null)
        drop table test
    create table test
    (
        [Id] int primary key
    )
    insert into test values (1 )
    insert into test values (2 )
    insert into test values (3 )
    insert into test values (4 )
    insert into test values (5 )
    insert into test values (6 )
    insert into test values (8 )
    insert into test values (9 )
    insert into test values (11)
    insert into test values (12)
    insert into test values (13)
    insert into test values (14)
    insert into test values (18)
    insert into test values (19)

    select Id from test t where  not exists(select 1 from test where Id=t.Id+1)
        or not exists(select 1 from test where Id=t.Id-1)

    drop table test

Answer：

转载于:https://www.cnblogs.com/chenjunbiao/archive/2008/10/13/1760184.html