在一张id连续的表中找出缺失的id

有这样一张表：

create table tb_lostid(
    id number(6,0) primary key not null,
    name nvarchar2(20) not null    
)

可以这样给它充值：

insert into tb_lostid 
select rownum,
       dbms_random.string('*',dbms_random.value(1,20))
from dual
connect by level<10000

然后删掉几条记录，以此来模拟误操作：

delete from tb_lostid where id in (3,107,234,888,907)

误操作发生后，任务是要把缺失的记录找出来，这里有两种解决方案。

一种是反联结方案，这种方案利用id连续的特点以id+1在自身中查找，找不到时则为缺失的id，最后去除尾记录就行了。

select a.id+1 from tb_lostid a where not exists (select null from tb_lostid b where b.id=a.id+1)
and a.id<> (select max(id) from tb_lostid)

运行结果：

SQL> select a.id+1 from tb_lostid a where not exists (select null from tb_lostid b where b.id=a.id+1)
  2  and a.id<> (select max(id) from tb_lostid);

    A.ID+1
----------
         3
       107
       234
       888
       907

 

另外一种左联结方案，它也是利用id+1在自身中找，找不到补空的记录即为要找的记录，最后也要去除尾记录。

select a.id+1 from tb_lostid a left join tb_lostid b on b.id=a.id+1
where b.id is null and
a.id<> (select max(id) from tb_lostid)

执行结果：

SQL> select a.id+1 from tb_lostid a left join tb_lostid b on b.id=a.id+1
  2  where b.id is null and
  3  a.id<> (select max(id) from tb_lostid);

    A.ID+1
----------
         3
       107
       234
       888
       907

--2020年2月24日--