sql - 查找表格中缺失的数字

Question

我想生成一个查询，该查询将生成一个 SQL 表中缺少的索引值列表。到目前为止，我所拥有的是

SELECT index - 1
FROM table
WHERE index - 1 NOT IN (
    SELECT DISTINCT index
    FROM table)
    AND
    index != 1;

这只会找到一系列缺失索引的第一个缺失索引。我想生成一个查询，输出 1 和 MAX(index) 之间的每个数字，这些数字不会在表中显示为 index。有办法吗？

Answer 1

您需要一个数字表来查找缺失的索引值

递归 CTE 方法

DECLARE @max_num INT = (SELECT Max(index)
   FROM   table);

WITH cte
     AS (SELECT 1 AS num
         UNION ALL
         SELECT num + 1
         FROM   cte
         WHERE  num < @max_num)
SELECT *
FROM   cte c
WHERE  NOT EXISTS (SELECT 1
                   FROM   table t
                   WHERE  c.num = t.index)
ORDER  BY c.num
OPTION (maxrecursion 0) 

另一种使用计数表生成数字的方法。

;WITH E1(N)
     AS (SELECT *
         FROM   (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) tc (N)), --10E+1 or 10 rows
     E2(N)
     AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), --10E+2 or 100 rows
     E4(N)
     AS (SELECT 1 FROM E2 a CROSS JOIN E2 b), --10E+4 or 10,000 rows max
     --E5(N) AS (SELECT 1 FROM E4 a CROSS JOIN E1 b),  -- 10000 * 10 = 100000
     cte(NUM)
     AS (SELECT ItemNumber = Row_number()OVER(ORDER BY N) FROM E4)
SELECT *
FROM   cte c
WHERE  NOT EXISTS (SELECT 1
                   FROM   table t
                   WHERE  c.num = t.index)
ORDER  BY c.num 

目前它生成 10000 个连续的行号，如果您的最大值超过 10000，那么您添加另一个交叉连接以增加数字