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

2021-12-24 00:00:00 sql oracle plsql gaps-and-islands

我正在做一个项目，为一所大学创建一个招生系统；这些技术是 Java 和 Oracle.

I am doing a project creating an admission system for a college; the technologies are Java and Oracle.

在其中一张表中，存储了预先生成的序列号.稍后，根据这些序列号，将输入申请人的表格数据.我的要求是，当输入过程完成时，我将必须生成一个 Lot wise 报告.如果在提供预先生成的序列号期间丢失了任何序列号.

In one of the tables, pre-generated serial numbers are stored. Later, against those serial numbers, the applicant's form data will be entered. My requirement is that when the entry process is completed I will have to generate a Lot wise report. If during feeding pre-generated serial numbers any sequence numbers went missing.

比如在一张表中，序号是7001、7002、7004、7005、7006、7010.从上面的系列可以看出，从7001到7010，缺失的数字是7003、7007、7008和7009

For example, say in a table, the sequence numbers are 7001, 7002, 7004, 7005, 7006, 7010. From the above series it is clear that from 7001 to 7010 the numbers missing are 7003, 7007, 7008 and 7009

Oracle 中是否有任何可用的 DBMS 函数来找出这些数字，或者是否有任何存储过程可以满足我的目的，然后请提出一种算法.

Is there any DBMS function available in Oracle to find out these numbers or if any stored procedure may fulfill my purpose then please suggest an algorithm.

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

I can find some techniques in Java but for speed I want to find the solution in Oracle.

推荐答案

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

A solution without hardcoding the 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.

相关文章