使用lag和lead函数查找顺序编号中缺失的序号

tzxd@webora9> spool aaa.log
tzxd@webora9> drop table test;
drop table test
           *
ERROR at line 1:
ORA-00942: table or view does not exist


tzxd@webora9> create table test(id,object_name) as
  2  select rownum,object_name from obj where rownum <= 20;

Table created.

tzxd@webora9> delete from test where id in (5,9,15);
在此,我们先删除3个不连续的数据,看看结果如何
3 rows deleted.

tzxd@webora9> col object_name format a32
tzxd@webora9> select id,object_name from test;

        ID OBJECT_NAME
---------- --------------------------------
         1 ACC_FEE_CODE
         2 ACC_USER_BILL_DTL_576_200401
         3 ACC_USER_BILL_DTL_576_200402
         4 ACC_USER_BILL_DTL_576_200403
         6 ACC_USER_BILL_DTL_576_200405
         7 ACC_USER_BILL_DTL_576_200406
         8 ALL_CELL_INFO
        10 ALL_CELL_INFO_TEMP
        11 ALL_CELL_INFO_TEMP_1
        12 ALL_CELL_INFO_TEMP_2
        13 ALL_CELL_INFO_TEMP_3
        14 ANALIZE_XD
        16 ANALIZE_XD_POST
        17 ANALIZE_XD_PRE
        18 AREA_200406
        19 AREA_200406_COUNT
        20 AREA_200406_TEMP1

17 rows selected.

tzxd@webora9> select id,id - lag_val diff_1,lead_val - id diff2,lag_val,lead_val,
object_name  2
  3  from (
  4     select id,lag(id,1,0) over (order by id) lag_val,
  5             lead(id,1,0) over (order by id ) lead_val,object_name
  6     from test
  7  )
  8  where lead_val - id <> id - lag_val
  9  /

        ID     DIFF_1      DIFF2    LAG_VAL   LEAD_VAL OBJECT_NAME
---------- ---------- ---------- ---------- ---------- --------------------------------
         4          1          2          3          6 ACC_USER_BILL_DTL_576_200403
/* 5 在4后面缺少diff_2 - diff_1 = 2 - 1 = 1个数据5 */
         6          2          1          4          7 ACC_USER_BILL_DTL_576_200405
         8          1          2          7         10 ALL_CELL_INFO
/* 9 在8后面缺少diff_2 - diff_1 = 2 - 1 = 1个数据9 */
        10          2          1          8         11 ALL_CELL_INFO_TEMP
        14          1          2         13         16 ANALIZE_XD
/* 15 在14后面缺少diff_2 - diff_1 = 2 - 1 = 1个数据15 */
        16          2          1         14         17 ANALIZE_XD_POST
        20          1        -20         19          0 AREA_200406_TEMP1

7 rows selected.

tzxd@webora9> spool off