有这样一张表:
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日--