Scripts: SQL 找出不连续的数值多种方法(gap)

像v$archived_gap一样有时需要找出一些序列中跳过的数值，如1，2，4，6，10，需要找出3,5,7-9，在数据库中使用SQL可以有多种方法,以下列出几种供参考

构建测试表

SQL> create table num_gap(id int);

Table created.

SQL> insert into num_gap select rownum from dual connect by rownum<=100;

100 rows created.

SQL> delete num_gap where id=10;

1 row deleted.

SQL> delete num_gap where id=30;

1 row deleted.

SQL> delete num_gap where id between 48 and 68;

21 rows deleted.

SQL> commit;

Commit complete.

查找跳过的号
方法1

SQL> select id+1 gap_start,next_-1 gap_end from (select id ,lead(id) over(order by id) next_ from num_gap) where id<>next_-1;

 GAP_START    GAP_END
---------- ----------
        10         10
        30         30
        48         68

方法2

SQL> select id+1 gap_start, next_-1 gap_end from (select id,(select min(id) from num_gap t2 where t2.id>t1.id) next_ from num_gap t1) where id<> next_-1;

 GAP_START    GAP_END
---------- ----------
        10         10
        30         30
        48         68

方法3

select start_no,end_no,end_no+1 gap_start,lead(start_no) over( order by 1)-1 gap_end
from
(
SELECT MIN(id) START_NO,MAX(id) END_NO
FROM (SELECT id ,id+ROWNUM*(-1) RANGE_ID FROM num_gap)
GROUP BY RANGE_ID
)
 order by 1;

  START_NO     END_NO  GAP_START    GAP_END
---------- ---------- ---------- ----------
         1          9         10         10
        11         29         30         30
        31         47         48         68
        69        100        101

方法4

select start_no,end_no,end_no+1 gap_start,lead(start_no) over( order by 1)-1 gap_end
from
(
select min(id) start_no, max(id) end_no
    from (
  select id, last_value(grp ignore nulls) over (order by id) new_grp
    from (
  select id,
         decode( nvl(lag(id) over (order by id),id), id-1, to_number(null), row_number() over (order by id) ) grp
    from num_gap t
         )
             )
   group by new_grp
   order by new_grp
)


  START_NO     END_NO  GAP_START    GAP_END
---------- ---------- ---------- ----------
         1          9         10         10
        11         29         30         30
        31         47         48         68
        69        100        101

方法5

SELECT * FROM (
  SELECT
    MAX(id)
      OVER(ORDER BY id) + 1 gap_start,
    LEAD(id)
      OVER(ORDER BY id) - 1 gap_end
  FROM num_gap
) WHERE gap_start <= gap_end;


 GAP_START    GAP_END
---------- ----------
        10         10
        30         30
        48         68