SQL команда выборки [дубликат]

#sql

        
             
                
                    
                        
                            This question already has answers here:
                            
                        
                    
                
                        
                            Как выбрать пропущеные ID?
                                
                                    (7 ответов)
                                
                        
                                Закрыт 3 года назад.
            
                    
Представим, что есть некая таблица Users, в которой есть идентификатор (primary key)
с именем id. Но по какой-то причине id были вырваны строки. И получилась такая вот таблица:

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

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

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

Ответы

Ответ 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 выведет,
но за-то очень просто :)


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


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