sql - 查找表中缺失的序列

Question

我正在使用 Oracle 10g 数据库。我正在尝试弄清楚如何编写一个简单的 sql 查询:

在表中查找缺失的数字，比如 86002895 到 86005197(含)之间，86002895 到 86005197 之间有 1955 行。

例如:当前场景:table_1:

tracking_no | id_value
86002895 | 10
86002896 | 10
86002899 | 10
86002900 | 10
86002910 | 10
86005196 | 10
86005197 | 10

预期结果 1:

" missing tracking_id " where id_value = 10 from table_1 ;

86002897

86002898
86002900到

86002910

86002910到

86005196

提前致谢

Answer 1

with data as 
(
  select tracking_no from table_1 where id_value = 10
),  
data_n as 
(
  select level + (select min(tracking_no) from data) n 
  from dual 
  connect by level <= (select max(tracking_no) - min(tracking_no) from data) 
)
select * from data_n
where not exists (select 1 from data where tracking_no = n);

如果要包含 86002895 和 86005197，请执行以下操作:

with data as 
(
  select tracking_no from table_1 
  where id_value = 10
  and   tracking_no between 86002895 and 86005197
),  
data_n as 
(
  select level + (select min(tracking_no) from data) n 
  from dual 
  connect by level <= (select max(tracking_no) - min(tracking_no) from data) 
)
select * from data_n
where not exists (select 1 from data where tracking_no = n);