Как проверить любое пропущенное число из ряда чисел?

Решение без жесткого кодирования 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

Это работает на 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
• Не требует знания всей последовательности (но, таким образом, не определяет, были ли пропущены самые первые или последние числа в последовательности)
• Перечисляет значения, окружающие отсутствующие списки чисел
• Перечисляет отсутствующие списки номеров как непрерывные группы (возможно, удобно для отчетности)
• Трагически терпит неудачу для очень больших списков отсутствующих чисел из-за ограничений списка.

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, как это сделал Стефан, однако вы не можете использовать подзапрос в этом операторе, который означает, что это не очень подходит для вас, так как вам нужно знать, каковы максимальные и минимальные значения вашей последовательности.

Я бы предположил, что табличная функция с вертикальной подкладкой может быть лучшим способом генерировать числа вам нужно сделать соединение. Чтобы это работало, вам понадобится объект в вашей базе данных для возврата значений:

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

Это сработало, но выбирается первая последовательность (начальное значение), поскольку у нее нет предшественника. Протестировано в 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) доступные последовательности

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

Улучшенный запрос:

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;

Примечание: это столбец, в котором мы находим пропущенное значение.