sql - 使用 SQL Server 查找列中的空白

Question

我有一个带有 int 类型列的表，它不是主键。我有一千条记录。

我想找到丢失的身份证。

我有这些数据：

1
2
3
4
6
8
11
14

我想要这个结果：5,7,9,10,12,13

你知道我该怎么做吗？

谢谢，

标签： sqlsql-server

Answer 1

将其作为范围更容易：

select (col + 1) as first_missing, (next_col - 1) as last_missing
from (select t.*, lead(col) over (order by col) as next_col
      from t
     ) t
where next_col <> col + 1;

如果您确实希望将此作为列表，我建议使用递归 CTE：

with cte as (
      select t.col, lead(col) over (order by col) as next_col, 1 as lev
      from t
      union all
      select cte.col + 1, next_col, lev + 1
      from cte
      where col + 1 < next_col
     )
select cte.col
from cte
where lev > 1;

注意：如果间隙可以超过 100，则需要OPTION (MAXRECURSION 0).

这是一个 db<>fiddle。