【Oracle】利用level/Connect by 制作连续序列，并借此实现对缺失id的查找

如果要在查询语句中创建某个区间的连续序列，可以这样做。

select seq from (select level as seq from dual connect by level<=100) a where a.seq>=20

生成的效果大家可以看一看：

SQL> select seq from (select level as seq from dual connect by level<=100) a where a.seq>=20;

       SEQ
----------
        20
        21
        22
        23
        24
        25
        26
        27
        28
        29
        30

       SEQ
----------
        31
        32
        33
        34
        35
        36
        37
        38
        39
        40
        41

       SEQ
----------
        42
        43
        44
        45
        46
        47
        48
        49
        50
        51
        52

       SEQ
----------
        53
        54
        55
        56
        57
        58
        59
        60
        61
        62
        63

       SEQ
----------
        64
        65
        66
        67
        68
        69
        70
        71
        72
        73
        74

       SEQ
----------
        75
        76
        77
        78
        79
        80
        81
        82
        83
        84
        85

       SEQ
----------
        86
        87
        88
        89
        90
        91
        92
        93
        94
        95
        96

       SEQ
----------
        97
        98
        99
       100

已选择81行。

其实到这里，只要把20换成待查字段的最低值，将100换成待查字段的最高值，再查哪些id在这里面没有，查缺失id的任务就解决了。

为实验完整起见，我们先创建一个只有id的test表。

create table test(
  id int ,
  primary key(id))

insert into test
select rownum from dual
connect by level<21;

然后删掉一些数据：

SQL> delete from test where id in (5,7,9,13,17);

已删除5行。

SQL> commit;

提交完成。

SQL> delete from test where id<3;

已删除2行。

SQL> comomit;

这些，test表中id从3开始，中间缺5，7，9，13，17，看我们的sql能否把它们找出来。

select b.seq from (select seq from (select level as seq from dual connect by level<=(select max(id) from test)) a where a.seq>=(select min(id) from test)) b where b.seq not in (select id from test)

执行效果：

SQL> select b.seq from (select seq from (select level as seq from dual connect by level<=(select max(id) from test)) a where a.seq>=(select min(id) from test)) b where b.seq not in (select id from test);

       SEQ
----------
         5
         7
         9
        13
        17

确如预料。

以上实验的全程记录：

SQL> create table test(
  2    id int ,
  3    primary key(id));

表已创建。

SQL> insert into test
  2  select rownum from dual
  3  connect by level<21;

已创建20行。

SQL> commit;

提交完成。

SQL> delete from test where id in (5,7,9,13,17);

已删除5行。

SQL> commit;

提交完成。

SQL> delete from test where id<3;

已删除2行。


SQL> commit;

提交完成。

SQL> select count(*) from test;

  COUNT(*)
----------
        13

SQL> select seq from (select level as seq from dual connect by level<=(select max(id) from test)) a where a.seq>=(select min(id) from test);

       SEQ
----------
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13

       SEQ
----------
        14
        15
        16
        17
        18
        19
        20

已选择18行。



SQL> select b.seq from (select seq from (select level as seq from dual connect by level<=(select max(id) from test)) a where a.seq>=(select min(id) from test)) b where b.seq not in (select id from test);

       SEQ
----------
         5
         7
         9
        13
        17

参考资料：《Oracle SQL疑难解析》P278（Grant Allen，Bob Bryla ，Darl Kuhn著）人民邮电出版社出版。这本书直击要害，鞭辟入里，是我的Oracle书籍中最好的一本。

-END-