查找列中缺少的数字

Question

我在T-SQL中有这个专栏：

 1
 2
 3
 7
 10

让SQl函数来检测序列4,5,6和8,9中的缺失数字。我尝试了一些类似的方法，比如如果( a-b >1 )，那么我们有一个缺失的数字

用coalesce但我不明白。感谢任何方向的人

Answer 1

您可以尝试这样做：

DELCARE @a
SET @a = SELECT MIN(number) FROM table
WHILE (SELECT MAX(number) FROM table ) > @a
BEGIN
IF @a NOT IN ( SELECT number FROM table )
PRINT @a
SET @a=@a+1
END

Answer 2

下面的查询将标识每个序列的起始位置和缺失的编号：

select t.col + 1 as MissingStart, (nextval - col - 1) as MissingSequenceLength
from (select t.col,
             (select min(t.col) from t t2 where t2.col > t.col) as nextval
      from t
     ) t
where nextval - col > 1

这是使用相关子查询来获取表中的下一个值。

Answer 3

我知道这是一个迟来的答案，但这里有一个查询，它使用递归表表达式来获取表中最小值和最大值之间的缺失值：

WITH CTE AS
(
    --This is called once to get the minimum and maximum values
    SELECT nMin = MIN(t.ID), MAX(t.ID) as 'nMax' 
    FROM Test t
    UNION ALL
    --This is called multiple times until the condition is met
    SELECT nMin + 1, nMax 
    FROM CTE
    WHERE nMin < nMax
)

--Retrieves all the missing values in the table.
SELECT c.nMin
FROM CTE c
WHERE NOT EXISTS
(
    SELECT ID
    FROM Test
    WHERE c.nMin = ID
)

这是使用以下模式进行测试的：

CREATE TABLE Test
(
    ID int NOT NULL
)

INSERT INTO Test
    Values(1)

INSERT INTO Test
    Values(2)

INSERT INTO Test
    Values(3)

INSERT INTO Test
    Values(7)

INSERT INTO Test
    Values(10)