oracle 查找序列号,Oracle 查找丢失数据序列号

昨天数据中心一个人来找我，可能手贱删除一条数据，或者档案号存在但是权利人名称和土地证号缺失。

档案号存在但是权利人名称和土地证号丢失很简单 ，只要判断一下is null就可以获得档案号。

但是数据被删除了来找缺失的数据我研究了半天，多方百度终于解决。

1.首先把档案号字段拆分成数字

select (replace(substr(c.DH,9,14),'-',''))

from (select * from T_ARCHIVE t where DH like 'J202-国土·1-2013%' and DH is not null) c

2.  (重点！)使用oracle的connect by level 构造一个序列(所有档案号都存在)

select * from (

select ('12013001000' +Level) dtll from dual

connect by level <=

(select max(replace(substr(d.DH,9,14),'-','')) from (select * from T_ARCHIVE t where DH like 'J202-国土·1-2013%' and DH is not null) d)-'12013001000')

3.只要判断 1步骤中的数据 not exists 2步骤的序列 就可以查找出丢失的档案号 (注意这里不能用not in ，我也不知道为什么 ，用了就会无限正在执行 QAQ)

select * from (

select ('12015001000' +Level) dtll from dual

connect by level <=

(select max(replace(substr(d.DH,9,14),'-','')) from (select * from T_ARCHIVE t where DH like 'J202-国土·1-2015%' and DH is not null) d)-'12015001000')

where not exists (select 1 from (select (replace(substr(c.DH,9,14),'-','')) tt

from (select * from T_ARCHIVE t where DH like 'J202-国土·1-2013%' and DH is not null) c ) s

where dtll=s.tt) order by dtll

省事我就没重新构造数据了，丢失的日期也同理可以获得。