oracle 中某序列不完整、有缺失，如何找出这些缺失的序列号并保存下来？

[pycollin]

oracle 中某序列不完整、有缺失，如何找出这些缺失的序列号并保存下来？

[dex_null]

dex@ORCL> insert into t select level from dual connect by level <= 100 ;

100 rows created.

dex@ORCL> commit ;

Commit complete.

dex@ORCL> delete t where n in (1,10,14,16,17,20,30,50) ;

8 rows deleted.

dex@ORCL> commit ;

Commit complete.

dex@ORCL> select count(*) from t  ;

  COUNT(*)
----------
        92


加删个边界值
dex@ORCL> delete t where n = 100 ;

1 row deleted.

dex@ORCL> commit
  2  ;

Commit complete.

dex@ORCL> select count(*) from t  ;

  COUNT(*)
----------
        91
序列是什么形式的自己构造
dex@ORCL> with full_seq as
  2   (select level as numb from dual connect by level <= 100)
  3  select seq.numb
  4    from full_seq seq
  5   where seq.numb not in (select n from t)
  6   order by seq.numb;

      NUMB
----------
         1
        10
        14
        16
        17
        20
        30
        50
       100

9 rows selected.

[forgetsam]

用递归查询 connect by 号 = prior 号+1

搜索连续号专题

[pycollin]

[Quote=引用 2 楼 的回复:]
SQL code


dex@ORCL> insert into t select level from dual connect by level <= 100 ;

100 rows created.

dex@ORCL> commit ;

Commit complete.

dex@ORCL> delete t where n in (1,10,14,16,17,20……
[/Quote]

真的谢谢你