更新时间:2023-01-11 07:43:16
您要查找某个范围内所有未分配的数字?如果是这样,您可以外部连接到数字表.下面的示例使用 CTE 即时创建一个,如果这是您的范围的最大大小,我建议使用一个包含至少 17,000 个数字的永久数字.
You are looking for all unassigned numbers in a range? If so you can outer join onto a numbers table. The example below uses a CTE to create one on the fly I would suggest a permanent one containing at least 17,000 numbers if that is the max size of your range.
DECLARE @StartRange int, @EndRange int
SET @StartRange = 790123401
SET @EndRange = 790123450;
WITH YourTable(ControlNumber) AS
(
SELECT 790123401 UNION ALL
SELECT 790123402 UNION ALL
SELECT 790123403 UNION ALL
SELECT 790123406
),
Nums(N) AS
(
SELECT @StartRange
UNION ALL
SELECT N+1
FROM Nums
WHERE N < @EndRange
)
SELECT N
FROM Nums
WHERE NOT EXISTS(SELECT *
FROM YourTable
WHERE ControlNumber = N )
OPTION (MAXRECURSION 0)