Вопрос по oracle, sql, plsql, gaps-and-islands – Как проверить пропущенный номер из серии чисел?

22

Я делаю проект по созданию системы приема в колледж; технологии Java и Oracle.

В одной из таблиц хранятся предварительно сгенерированные серийные номера. Позже, с этими серийными номерами, будут введены данные формы заявителя. Мое требование заключается в том, что, когда процесс ввода будет завершен, мне нужно будет сформировать отчет по лотам. Если во время кормления предварительно сгенерированные серийные номера какие-либо порядковые номера пропали без вести.

Например, скажем в таблице, порядковые номера 7001, 7002, 7004, 7005, 7006, 7010. Из вышеприведенных серий ясно, что с 7001 по 7010 пропущено число 7003, 7007, 7008 и 7009.

Есть ли какая-либо функция СУБД, доступная в Oracle, чтобы узнать эти числа, или если какая-либо хранимая процедура может выполнить мою цель, тогда предложите алгоритм.

Я могу найти некоторые методы в Java, но для скорости я хочу найти решение в Oracle.

Я добавил тег пробелы и острова. его поиск, вероятно, даст достаточное количество предшествующего уровня техники, включая рекурсивные запросы. wildplasser

Ваш Ответ

8   ответов
2

= 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;
Для downvoter: пожалуйста, объясните. Этот вопрос помечен как "sql" который является (должен быть) стандартным sql. CTE 's являются частью этого.
Это был не тот, кто отрицательно проголосовал, но, если честно, он также помечал Oracle, и этот синтаксис неверен.
Хорошо, мне сказали, что CTE реализован в oracle see:stackoverflow.com/questions/6064970/oracle-cte-merge, Конечно, конструкция connect-by / prior существовала несколько лет, но синтаксис CTE является, по крайней мере, частью стандарта, и всегда есть причина для некоторого разнообразия, даже если оно стандартное. Как я уже сказал в своем ответе: могут существовать незначительные различия в синтаксисе (например, пропуск ключевого слова RECURSIVE). И наконец: по крайней мере, запрос работает для меня (возможно, с некоторыми изменениями для других тоже). Здесь были и другие ответы, которыеdont Работа.
1

Relies on Oracle's LAG function Does not require knowledge of the complete sequence (but thus doesn't detect if very first or last numbers in sequence were missed) Lists the values surrounding the missing lists of numbers Lists the missing lists of numbers as contiguous groups (perhaps convenient for reporting) Tragically fails for very large lists of missing numbers, due to listagg limitations

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"                  
13

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
0
 SELECT ROWNUM "Missing_Numbers" FROM dual CONNECT BY LEVEL <= (SELECT MAX(a) FROM test1)
 MINUS
 SELECT a FROM test1 ;
Не понизить, потому что этоdoes работать, если вы измените значенияa до 1, 2, 3 ... 10, а не 7001-7010, отмеченных OP, но ... ну, это не сработает, если указанные числа равны 7001-7010, как отмечено в вопросе.dbfiddle here
40

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.
Это заставляет мой ответ выглядеть до смешного слишком сложным! +1
Я сам некоторое время изучал логику и решил, что не нужно тратить время на это. Я думаю, я должен был сделать Google хорошей практикой. Поэтому +1 к этому ответу.
-1
 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
Недостающие порядковые номера.
Пожалуйста, отредактируйте свое сообщение, чтобы обеспечить контекст для вашего ответа. Ответы только для кода полезны только частично:stackoverflow.com/help/how-to-answer
1

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 часть.

Вам нужноknow что значение равно 9. Как вы это знаете?
Вот что я написал: вам нужно знать диапазон вашей последовательности. Если я правильно понимаю задачу, это, вероятно, известно. Или я тебя неправильно понял?
1

но выбирает первую последовательность (начальное значение), поскольку у нее нет предшественника. Протестировано в 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 где я больше первой строки, например(7004 - 1 = 7003 and 7007 - 1 = 7006) которые доступны последовательности

Я думаю, что вы можете улучшить этот простой запрос

Это предполагает, что в таблице хранятся все номера последовательностей. В Oracle нет необходимости делать это.
будет Oracleconnect by лучше, чем это?
Мне нравится это простое решение, но если у вас есть несколько пропущенных номеров в последовательности (7,8,9), оно обнаружит только одно из них.
ответ с наибольшим количеством голосов здесь использует 2 агрегатные функции - как насчет производительности?

Похожие вопросы