SQL-Ex blog

Имеются id: -1, 1, 2, 3, 4, 5..., но нет 6 или 7. (Или 0). Если кто-нибудь попросит вас найти все id, которые были удалены или пропущены, как это сделать?

Использование GENERATE_SERIES в SQL Server 2022 и новее

Новая функция GENERATE_SERIES делает то, о чем говорит ее название: генерирует последовательность чисел. Мы можем выполнить соединение таблицы Users с этой последовательностью, чтобы найти все значения последовательности, для которых нет совпадающих строк в таблице Users:

DECLARE @FirstId INT, @LastId INT;

SELECT @FirstId = MIN(Id),

	@LastId = MAX(Id)

	FROM dbo.Users;

SELECT gs.value

FROM GENERATE_SERIES(@FirstId, @LastId, 1) gs

LEFT OUTER JOIN dbo.Users u ON gs.value = u.Id

WHERE u.Id IS NULL;

Поначалу LEFT OUTER JOIN может вам показаться несколько неестественным, но работает отлично:



Что такое, спросите вы? Почему GENERATE_SERIES подчеркнуто красным? Ну, SQL Server Management Studio не обновилась синтаксисом T-SQL, который был в последнем релизе.

К счастью, Microsoft разделила установки приложений для SSMS и самого ядра SQL Server именно по этой причине - долгое время выпуска SSMS не позволяло команде разработчиков ядра ускорить выпуск, поэтому они поместили менее часто обновляемую SSMS в свой собственный установщик.

(Я правильно понял? Простите меня, если это не так.)

Использование таблицы чисел в более старых версиях

Если вы еще не работаете на SQL Server 2022, то можете создать собственную таблицу чисел, используя следующие примеры. Только убедитесь, что ваша таблица чисел содержит по крайней мере столько строк, сколько номеров id вы ищете. Вот пример с таблицей на 100000000 строк:

DROP TABLE IF EXISTS dbo.Numbers;

CREATE TABLE dbo.Numbers (Number INT PRIMARY KEY CLUSTERED);  

INSERT INTO dbo.Numbers(Number)

SELECT TOP 10000000 row_number() over(order by t1.number) as N

FROM master..spt_values t1 

    CROSS JOIN master..spt_values t2

    CROSS JOIN master..spt_values t3

GO

Затем мы будем использовать это в том же ключе, что и GENERATE_SERIES:

DECLARE @FirstId INT, @LastId INT;

SELECT @FirstId = MIN(Id),

	@LastId = MAX(Id)

	FROM dbo.Users;

SELECT n.Number

FROM dbo.Numbers n

LEFT OUTER JOIN dbo.Users u ON n.Number = u.Id

WHERE u.Id IS NULL

  AND n.Number > @FirstId

  AND n.Number < @LastId

ORDER BY n.Number;

Это дает похожие результаты, но не идентичные:



В чем отличие? Да, этот метод не включает 0! Когда я наполнял мою таблицу чисел, то строил список только положительных целых. Единственной наиболее часто встречающейся мне ошибкой при использовании таблиц чисел является то, что они не охватывают всех необходимых нам чисел. Убедитесь, что они содержат как самое меньшее, так и самое большее из требуемых вам значений - проблема, которой мы не имеем с GENERATE_SERIES, поскольку там мы просто указываем начальное и конечное значения, а SQL Server делает все остальное.

Я не хочу погружаться глубже в другие способы решения этой проблемы, Itzik Ben-Gan введет вас в курс дела. Что до меня, мне нравится решать эту проблему быстро и легко с помощью GENERATE_SERIES. Кроме того, я ленив.