SQL Server:如何从一系列整数中检测未保留的值

Question

我遇到了一个问题，例如：我有一个整数的id范围（从1到1000），这个范围应该是SQL Server表中的id，我想检测这个范围中的哪些数字不在表中，很抱歉我的英语不好，谢谢

Answer 1

另一个更简单的选项是使用以下查询

SELECT number 
FROM master..spt_values
WHERE number BETWEEN 1 AND 1000
 AND NOT EXISTS ( SELECT 1 
                  FROM Your_Table t    --<-- your table where you are checking
                  WHERE t.ID = number)    -- the missing values 
GROUP BY number

只有当您正在寻找大约1000个值时，上述解决方案才是好的。对于更多的值，你需要稍微修改一下，比如

-- Select the maximum number or IDs you want to check 
DECLARE @Max_Num INT = 10000;
;WITH CTE AS 
(
  SELECT TOP (@Max_Num) ROW_NUMBER() OVER ( ORDER BY (SELECT NULL)) numbers 
  FROM master..spt_values v1 cross join master..spt_values v2
) 
SELECT c.numbers 
FROM CTE c
WHERE NOT EXISTS (SELECT 1 
                  FROM Your_table t
                  WHERE t.ID = c.numbers)

Answer 2

查找"孔"的一种方法是生成所有可能值的列表，然后查找不存在的值。如果您可以用一个缺失值的列表以及后面的后续值的数量来生存，那么您可以用另一种方法来实现这一点。

SQL Server 2012+支持lead()和lag()。除了初始缺失值外，以下内容几乎可以获得所有内容：

select t.id + 1 as missingid,
       (coalesce(t.nextid, 1000) - t.id - 1) as nummissing
from (select t.*, lead(t.id) over (order by t.id) as nextid
      from table t
      t.id between 1 and 1000 
     ) t
where t.nextid > t.id + 1 or
      (t.nextid is null and t.id <> 1000)

你可以通过一个特殊的逻辑得到这些：

select (case when t.previd is null then 1
             else t.id + 1
        end) as missingid,
       (case when t.previd is null then t.id - 1
             else (coalesce(t.nextid, 1000) - t.id - 1) 
        end) as nummissing
from (select t.*, lead(t.id) over (order by t.id) as nextid,
             lag(t.id) over (order by t.id) as previd
      from table t
      where t.id between 1 and 1000 and
     ) t
where (t.nextid > t.id + 1 or
       (t.nextid is null and t.id <> 1000)
       (t.previd is null and t.id <> 1)
      )

Answer 3

这听起来像是拥有数字表有帮助的许多场景之一：

SELECT *
FROM lkp_Numbers a
WHERE NOT EXISTS (SELECT 1
                  FROM YourTable b
                  WHERE a.num = b.Id)
  AND num <= 1000

我用它来创建一个数字表：

DROP TABLE lkp_Numbers
DECLARE @RunDate datetime 
SET @RunDate=GETDATE()
SELECT TOP 1000 IDENTITY(int,1,1) AS Num
    INTO lkp_Numbers
    FROM sys.objects s1, sys.objects s2, sys.objects s3
ALTER TABLE lkp_Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Num)

创建数字表的方法可以在这里找到：创建和填充数字表的最佳方法是什么？