SQL команда выборки

Question

Представим, что есть некая таблица Users, в которой есть идентификатор (primary key) с именем id. Но по какой-то причине id были вырваны строки. И получилась такая вот таблица:

id 1,2,3,5,6,7,8,10,12

Вопрос, как получить с использованием только одного select набор, который включает только остутствующие числа. То есть какой сделать sql запрос ?

Задали вопрос на собеседовании, стало очень интересно.

Answer 1

Если это не диапозон (как в вашем случае), то задача решается очень тривиально и просто. Достаточно сделать подзапрос в WHERE, где нет следующего значения:

SELECT (`numbers`.`number`+1) AS `not_exists_number`
FROM `numbers`
WHERE
    (SELECT 1
     FROM `numbers` AS `add_table`
     WHERE `add_table`.`number` = (`numbers`.`number` + 1)) IS NULL
ORDER BY `numbers`.`number`;

Если действительно нужен один SELECT, то данное решение перепишем на JOIN

SELECT l.number + 1 AS missing
FROM numbers AS l
LEFT JOIN numbers AS r ON l.number + 1 = r.number
WHERE r.number IS NULL;

Правда тут проблема в двух решениях, что последнее значение будет всегда больше последнего в таблице на 1, но его можно отсечь. Если у Вас было последнее 26, то он еще 27 выведет, но за-то очень просто :)

Answer 2

DECLARE @ID INT
DECLARE @MaxID INT
DECLARE @MissingID TABLE ( [ID] INT )

SELECT @MaxID = [ID] FROM Users

SET @ID = 1
WHILE @ID <= @MaxID
BEGIN
    IF NOT EXISTS (SELECT * FROM Users
                   WHERE [ID] = @ID)
        INSERT INTO @MissingID ( [ID] )
        VALUES ( @ID )

    SET @ID = @ID + 1
END

SELECT * FROM @MissingID

Answer 3

Решение взято отсюда. Попробовала в MS SQL Server 2008, работает. Правда, по Вашему условию мне не совсем понятно, разрешены ли вложенные select'ы. :)

SELECT DISTINCT number
FROM master..spt_values
WHERE number BETWEEN 1 AND (SELECT MAX(id) FROM MyTable)
AND number NOT IN (SELECT id FROM MyTable)