일련의 번호에서 누락된 번호를 확인하는 방법은 무엇입니까?

일련의 번호에서 누락된 번호를 확인하는 방법은 무엇입니까?

저는 대학 입학 시스템을 만드는 프로젝트를 하고 있습니다. 기술은 자바와 오라클입니다.

테이블 중 하나에는 사전 생성된 일련 번호가 저장됩니다.나중에 해당 일련 번호에 대해 지원자의 양식 데이터가 입력됩니다.제 요구사항은 입력 프로세스가 완료되면 Lottwise 보고서를 생성해야 한다는 것입니다.사전 생성된 일련 번호를 공급하는 동안 시퀀스 번호가 누락된 경우.

예를 들어, 표에서 시퀀스 번호가 7001, 7002, 7004, 7005, 7006, 7010이라고 가정합니다.위의 시리즈에서 7001부터 7010까지 누락된 숫자는 7003, 7007, 7008 및 7009임이 분명합니다.

오라클에서 이 숫자들을 확인할 수 있는 DBMS 기능이 있는지 또는 저장 프로시저가 제 목적을 달성할 수 있는지 알고리즘을 제안해 주십시오.

Java에서 몇 가지 기술을 찾을 수 있지만 속도를 위해서는 Oracle에서 솔루션을 찾고 싶습니다.

하드 코딩이 없는 솔루션 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.

사용해 보십시오.

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

이것은 작동했지만 선행 시퀀스가 없기 때문에 첫 번째 시퀀스(시작 값)를 선택합니다.SQL Server에서 테스트되었지만 Oracle에서 작동해야 함

SELECT
    s.sequence  FROM seqs s
WHERE
    s.sequence - (SELECT sequence FROM seqs WHERE sequence = s.sequence-1) IS NULL

다음은 테스트 결과입니다.

  Table
  -------------
  7000
  7001
  7004
  7005
  7007
  7008

  Result
  ----------
  7000
  7004
  7007

할당되지 않은 시퀀스를 가져오려면 다음을 수행합니다.value[i] - 1여기서 i는 더 큰 첫 번째 행입니다. 예를 들어. (7004 - 1 = 7003 and 7007 - 1 = 7006) 가능한 는 다음과 같습니다.

저는 당신이 이 간단한 질문을 개선할 수 있다고 생각합니다.

이 작업은 postgres >= 8.4에서 작동합니다.CTE 구문을 약간 수정하면 오라클 및 마이크로소프트에서도 작동할 수 있습니다.

-- 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;

다음은 다음과 같은 솔루션입니다.

• Oracle의 LAG 기능에 의존
• 전체 시퀀스에 대한 지식이 필요하지 않습니다(단, 시퀀스의 맨 처음 또는 마지막 번호가 누락되었는지 여부는 감지하지 못합니다).
• 결측 번호 목록 주위의 값을 나열합니다.
• 누락된 번호 목록을 연속 그룹으로 나열합니다(보고에 편리할 수 있음).
• listagg 제한으로 인해 매우 큰 누락 번호 목록에 대해 비극적으로 실패합니다.

SQL:

WITH MentionedValues /*this would just be your actual table, only defined here to provide data for this example */
        AS (SELECT *
              FROM (    SELECT LEVEL + 7000 seqnum
                          FROM DUAL
                    CONNECT BY LEVEL <= 10000)
             WHERE seqnum NOT IN (7003,7007,7008,7009)--omit those four per example
                                       ),
     Ranges /*identifies all ranges between adjacent rows*/
        AS (SELECT seqnum AS seqnum_curr,
                   LAG (seqnum, 1) OVER (ORDER BY seqnum) AS seqnum_prev,
                   seqnum - (LAG (seqnum, 1) OVER (ORDER BY seqnum)) AS diff
              FROM MentionedValues)
SELECT Ranges.*,
       (    SELECT LISTAGG (Ranges.seqnum_prev + LEVEL, ',') WITHIN GROUP (ORDER BY 1)
              FROM DUAL
        CONNECT BY LEVEL < Ranges.diff) "MissingValues" /*count from lower seqnum+1 up to lower_seqnum+(diff-1)*/
  FROM Ranges
 WHERE diff != 1 /*ignore when diff=1 because that means the numers are sequential without skipping any*/
;

출력:

SEQNUM_CURR SEQNUM_PREV DIFF MissingValues
7004        7002        2    "7003" 
7010        7006        4    "7007,7008,7009"                  

시나리오에 대한 답변을 얻는 간단한 방법은 다음과 같습니다.

create table test1 ( a number(9,0));

insert into test1 values (7001);
insert into test1 values (7002);
insert into test1 values (7004);
insert into test1 values (7005);
insert into test1 values (7006);
insert into test1 values (7010);
commit;

select n.n from (select ROWNUM + 7001 as n from dual connect by level <= 9) n 
   left join test1 t on n.n = t.a where t.a is null;

선택하면 예제에서 답을 얻을 수 있습니다.이것은 숫자가 어느 범위에 있는지 미리 알고 있고 범위가 너무 크면 안 되는 경우에만 의미가 있습니다.는 첫번숫다서오이합야니다어셋의 .ROWNUM부분과 시퀀스의 길이는 레벨의 한계입니다.connect by일부.

제가 제안했을 것입니다.connect by level그러나 Stefan이 했던 것처럼 이 문장에서 하위 쿼리를 사용할 수 없습니다. 즉, 시퀀스의 최대값과 최소값을 알아야 하기 때문에 이 쿼리는 사용자에게 적합하지 않습니다.

조인에 필요한 숫자를 생성하려면 파이프라인 테이블 함수가 가장 좋은 방법일 수 있습니다.이 작업을 수행하려면 다음 값을 반환할 개체가 데이터베이스에 필요합니다.

create or replace type t_num_array as table of number;

그런 다음 기능:

create or replace function generate_serial_nos return t_num_array pipelined is

   l_first number;
   l_last number;

begin

   select min(serial_no), max_serial_no)
     into l_first, l_last 
     from my_table
          ;

   for i in l_first .. l_last loop
      pipe row(i);
   end loop;

   return;

end generate_serial_nos;
/

이 기능을 사용하면 다음은 최소와 최대 사이의 일련 번호 목록을 반환합니다.

select * from table(generate_serial_nos);

즉, 누락된 일련 번호를 찾기 위한 쿼리는 다음과 같습니다.

select serial_no
  from ( select * 
           from table(generate_serial_nos) 
                ) generator 
  left outer join my_table actual
    on generator.column_value = actual.serial_no
 where actual.serial_no is null

 SELECT ROWNUM "Missing_Numbers" FROM dual CONNECT BY LEVEL <= (SELECT MAX(a) FROM test1)
 MINUS
 SELECT a FROM test1 ;

향상된 쿼리:

SELECT ROWNUM "Missing_Numbers" FROM dual CONNECT BY LEVEL <= (SELECT MAX(a) FROM test1)
 MINUS
SELECT ROWNUM "Missing_Numbers" FROM dual CONNECT BY LEVEL < (SELECT Min(a) FROM test1)
 MINUS
SELECT a FROM test1;

참고: a는 결측값을 찾는 열입니다.

하위 쿼리로 시도:

SELECT A.EMPNO + 1 AS MissingEmpNo
FROM tblEmpMaster AS A
WHERE A.EMPNO + 1 NOT IN (SELECT EMPNO FROM tblEmpMaster)

 select    A.ID + 1 As ID
From [Missing] As A
Where A.ID + 1 Not IN (Select ID from [Missing])
And A.ID < n

Data: ID
1
2
5
7
Result: ID
3
4
6

언급URL : https://stackoverflow.com/questions/10968493/how-to-check-any-missing-number-from-a-series-of-numbers