sql - 如何在从参数开始并按顺序递增的表字段中找到缺失的数字？

Question

假设我有一个 sql server 表:

公司名称

2佛瑞德3佛瑞德4佛瑞德6佛瑞德7佛瑞德8佛瑞德11佛瑞德

我需要一种有效的方法来传入参数 [StartingNumber] 并按顺序从 [StartingNumber] 开始计数，直到找到丢失的数字。

例如，请注意表中缺少 1、5、9 和 10。

如果我提供参数 [StartingNumber] = 1，它会检查 1 是否存在，如果存在，它会检查 2 是否存在，依此类推，因此 1 将在此处返回。

如果 [StartNumber] = 6，该函数将返回 9。

在 c# 伪代码中，它基本上是:

int ctr = [StartingNumber]
while([SELECT NumberTaken FROM tblNumbers Where NumberTaken = ctr] != null)    
    ctr++;

return ctr;

该代码的问题在于，如果表中有数千个数字，则效率似乎很低。此外，我可以用 c# 代码或存储过程中效率更高的方式编写它。

谢谢您的帮助

Answer 1

我称我的表为空白，并使用了以下内容:

declare @StartOffset int = 2
; With Missing as (
    select @StartOffset as N where not exists(select * from Blank where ID = @StartOffset)
), Sequence as (
    select @StartOffset as N from Blank where ID = @StartOffset
    union all
    select b.ID from Blank b inner join Sequence s on b.ID = s.N + 1
)
select COALESCE((select N from Missing),(select MAX(N)+1 from Sequence))

您基本上有两种情况 - 您的起始值丢失(因此缺少的 CTE 将包含一行)，或者它存在，因此您使用递归 CTE(序列)向前计数，并从中获取最大值并添加 1

从评论中编辑。是的，在顶部创建另一个具有过滤条件的 CTE，然后在查询的其余部分中使用它:

declare @StartOffset int = 2
; With BlankFilters as (
    select ID from Blank where hasEntered <> 1
), Missing as (
    select @StartOffset as N where not exists(select * from BlankFilters where ID = @StartOffset)
), Sequence as (
    select @StartOffset as N from BlankFilters where ID = @StartOffset
    union all
    select b.ID from BlankFilters b inner join Sequence s on b.ID = s.N + 1
)
select COALESCE((select N from Missing),(select MAX(N)+1 from Sequence))

这现在可能会返回表中确实存在的行，但 hasEntered=1

表格:

create table Blank (
    ID int not null,
    Name varchar(20) not null
)
insert into Blank(ID,Name)
select 2 ,'Fred' union all
select 3 ,'Fred' union all
select 4 ,'Fred' union all
select 6  ,'Fred' union all
select 7 ,'Fred' union all
select 8 ,'Fred' union all
select 11 ,'Fred'
go