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

Ответ 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.

Ответ 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

Ответ 3

Я бы предложил connect by level как Stefan сделал, однако вы не можете использовать подзапрос в этом утверждении, а это значит, t действительно подходит для вас, так как вам нужно знать, каковы максимальные и минимальные значения вашей последовательности.

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

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

Ответ 4

Один простой способ получить ответ для вашего сценария:

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.

Ответ 5

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

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

Ответ 6

Здесь решение, которое:

• Опирается на функцию 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"                  

Ответ 7

Это работает на 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;

Ответ 8

 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