如何在预言机中漏掉100个数字

Question

我需要在oracle中的一个表列中找到丢失的数字，其中丢失的数字必须乘以100，这意味着如果找到了至少在2000到2099之间的一个数字，则必须返回2000到2099

这里有一个例子可以说明我需要什么：

create table test1 ( a number(9,0));
insert into test1 values (2001);
insert into test1 values (2002);
insert into test1 values (2004);
insert into test1 values (2105);
insert into test1 values (3006);
insert into test1 values (9410);
commit;

结果必须是200020032005到20992100到21042106到21993000到30053007到30999400到94099411到9499。

我从这个查询开始，但它显然没有返回我需要的：

SELECT Level+(2000-1) FROM dual  CONNECT BY LEVEL  <= 9999 
MINUS SELECT a FROM test1;

Answer 1

您可以使用如下的hiearchy查询：

SQL> SELECT A FROM (
2  SELECT A + COLUMN_VALUE - 1 AS A
3    FROM ( SELECT DISTINCT TRUNC(A, - 2) A
4         FROM TEST_TABLE) T
5   CROSS JOIN TABLE ( CAST(MULTISET(
6  SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100
7     ) AS SYS.ODCINUMBERLIST) ) LEVELS
8  )
9  MINUS
10  SELECT A FROM TEST_TABLE;
A
----------
2000
2003
2005
2006
2007
2008
2009
.....
.....

Answer 2

我喜欢使用标准的递归查询。

with nums (a, max_a) as (
select min(a), max(a) from test1
union all 
select a + 1, max_a from nums where a < max_a
)
select n.a
from nums n
where not exists (select 1 from test1 t where t.a = n.a)
order by n.a

with子句取表中a的最小值和最大值，并生成其间的所有数字。然后，外部查询将筛选表中不存在的查询。

如果你想生成缺失数字的范围，而不是一个全面的列表，你可以使用窗口函数：

select a + 1 start_a, lead_a - 1 end_a
from (
select a, lead(a) over(order by a) lead_a
from test1
) t
where lead_a <> a + 1

DB Fiddle演示

---

编辑：

如果您希望丢失的值在数千的范围内，那么我们可以稍微调整递归解决方案：

with nums (a, max_a) as (
select distinct floor(a / 100) * 100 a, floor(a / 100) * 100 + 100 from test1
union all 
select a + 1, max_a from nums where a < max_a
)
select n.a
from nums n
where not exists (select 1 from test1 t where t.a = n.a)
order by n.a

Answer 3

假设您为范围定义了固定的上限和下限，那么只需要使用NOT EXISTS(如(来消除当前查询的结果

SQL> exec :min_val:=2000
SQL> exec :min_val:=2499
SQL> SELECT *
FROM
(
SELECT level + :min_val - 1 AS nr
FROM dual        
CONNECT BY level <= :max_val - :min_val + 1
)
WHERE NOT EXISTS ( SELECT * FROM test1 WHERE a = nr ) 
ORDER BY nr;
/

演示