Как выбрать пропущеные ID?

#sql

Есть поле ID в некой таблице Tab, некоторые номера пропущены, например:
1,2,4,5,6,8
Как выбрать номера 3 и 7?
Я давно делал такой запрос и вот не могу вспомнить как )) Там помню в условии было
ID+1    

Ответы

Ответ 1

Алгоритм. Для каждого Id смотрим следующий, если он не равен текущему+1 - находим
пропуск.
Пусть есть таблица SomeTable, и у неё записи со значениями id: 5,7,8,11,15
Найдём интервалы пропусков:

SELECT id+1 as start_interval, next_id-1 as finish_interval
FROM(
  SELECT id, LEAD(id)OVER(ORDER BY Id)as next_id
  FROM SomeTable
)T
WHERE id+1 <> next_id


выдаст:

start_interval  finish_interval
6   6
9   10
12  14


Если нас интересует интервал от 1 до первого Id, искусственно внедрим в запрос id=0:

SELECT id+1 as start_interval, next_id-1 as finish_interval
FROM(
  SELECT id, LEAD(id)OVER(ORDER BY Id)as next_id
  FROM (
    SELECT 0 Id
    UNION ALL
    SELECT Id FROM SomeTable
  )T
)T
WHERE id+1 <> next_id


выдаст:

start_interval  finish_interval
1   4
6   6
9   10
12  14


Примечание: Использовал MS SQL начиная с версии 2012, в Oracle и PostgreSQL вроде
тоже должно работать.

Для версий MS SQL ниже 2012 можно использовать следующий код, который практически
не уступает по производительности LEAD():

SELECT id1+1 start_interval, id2-1 finish_interval
FROM(
  SELECT 
    MAX(CASE WHEN dN=1 THEN Id END)id1,
    MAX(CASE WHEN dN=0 THEN Id END)id2
  FROM(
    SELECT ROW_NUMBER()OVER(ORDER BY Id)as N, Id
    FROM SomeTable
  )T1 , ( SELECT 0 as dN UNION ALL SELECT 1 )T2
  GROUP BY N+dN
)T
WHERE Id1+1 <> Id2


Для MySQL будет выглядеть примерно так

SELECT last_id+1 as start_interval, currentId-1 as finish_interval
FROM(
  SELECT @Id last_id, @Id := Id currentId
  FROM SomeTable, (SELECT @Id := NULL)T
  ORDER BY Id
)T
WHERE last_id+1 <> current_id


Ответ 2

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

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

with numbers as
(
    select min(id) as number
    from    tab
    union all
    select number + 1
    from numbers
    where number < any (select id from tab)
)
select numbers.number
from    tab tab
        right join numbers
                on tab.id = numbers.number
where  tab.id is null
option (maxrecursion 0)


Ответ 3

postgresql:  

SELECT * 
  FROM generate_series(1, greatest((SELECT max(id)-1 FROM table), 1) s
 WHERE NOT EXISTS (SELECT id FROM table WHERE id = s)


max(id)-1 - зачем нам max(id), если он точно занят?
Написав max(id)+1, имеем шанс получить подходящий id для новой записи сразу же. Либо
из дырки в нумерации, либо следующий за максимальным. Если конечная задача именно такая.
generate_series(start, stop) - генерирует последовательность чисел от start до stop
включительно
greatest(a, b, ...) - возвращает наибольшее не null значение из списка. Используется
для защиты от генерации пустой последовательности, когда таблица table пустая (stop
будет = null) или содержит только одну запись с минимальным id (stop будет = 0)
Предполагается минимальный id = 1


Ответ 4

oracle

select z.aaa
from (select level aaa from dual connect by level <= (select max(id) from table)) z
left join table x on x.id = z.aaa
where x.id is null


Ответ 5

Мое решение на MySQL, где не поддерживается LEAD:

CREATE TABLE test (
  id INT NOT NULL PRIMARY KEY
);

INSERT INTO test (id) VALUES (1), (2), (3), (6), (8), (9), (12);

SELECT
  id1 AS 'FROM',
  id3 AS 'TO' 
FROM (
SELECT
    t1.id AS id1,
    t2.id AS id2,
    (
      SELECT id FROM test t3 
      WHERE t3.id > t1.id
      ORDER BY id
      LIMIT 1
    ) AS id3 FROM test t1 
LEFT JOIN test t2 ON t2.id = t1.id + 1) A
WHERE id1 IS NOT NULL AND id2 IS NULL AND id3 IS NOT NULL


Ответ 6

Для MySQL шустро работает вот это (~1000000 записей):

SELECT t1.id + 1 AS empy_id
FROM movie AS t1
LEFT JOIN movie AS t2 ON t2.id = t1.id +1
WHERE t2.id IS NULL


Отображение строк 0 - 49 (78605 всего, Запрос занял 0.0003 сек.)


Ответ 7

select * from tab where ID is not (select ID from tab)