如何检查一系列数字中任何缺失的数字？

Question

我正在做一个项目，为一所大学创建一个录取系统；技术是Java和Oracle。

在其中一个表中，存储了预先生成的序列号。随后，根据这些序列号，将输入申请人的表单数据。我的要求是，当进入过程完成后，我将不得不生成许多明智的报告。在输入预先生成的序列号的过程中，是否有任何序列号丢失。

例如，在表中，序列号是7001、7002、7004、7005、7006、7010。从上面的序列可以清楚地看出，从7001到7010，缺少的数字是7003、7007、7008和7009

Oracle中是否有可用的DBMS函数来找出这些数字，或者如果有任何存储过程可以满足我的目的，那么请建议一个算法。

我可以在Java中找到一些技术，但是为了提高速度，我想在Oracle中找到解决方案。

Answer 1

无需硬编码9的解决方案：

select min_a - 1 + level
     from ( select min(a) min_a
                 , max(a) max_a
              from test1
          )
  connect by level <= max_a - min_a + 1
    minus
   select a
     from test1

结果：

MIN_A-1+LEVEL
-------------
         7003
         7007
         7008
         7009

4 rows selected.

Answer 2

试试这个：

SELECT t1.SequenceNumber + 1 AS "From",
       MIN(t2.SequenceNumber) - 1 AS "To"
FROM MyTable t1
JOIN MyTable t2 ON t1.SequenceNumber < t2.SequenceNumber 
GROUP BY t1.SequenceNumber
HAVING t1.SequenceNumber + 1 < MIN(t2.SequenceNumber)

以下是序列7001、7002、7004、7005、7006、7010的结果：

From  To
7003  7003
7007  7009

Answer 3

这适用于postgres >= 8.4。对CTE语法稍作修改，也可以使其适用于oracle和microsoft。

-- EXPLAIN ANALYZE
WITH missing AS (
    WITH RECURSIVE fullhouse AS (
        SELECT MIN(num)+1 as num
        FROM numbers n0
        UNION ALL SELECT 1+ fh0.num AS num
        FROM fullhouse fh0
        WHERE EXISTS (
                SELECT * FROM numbers ex
                WHERE ex.num > fh0.num
                )
        )
        SELECT * FROM fullhouse fh1
        EXCEPT ( SELECT num FROM numbers nx)
        )
SELECT * FROM missing;