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

Question

假设我有一个 sql server 表：

NumberTaken CompanyName

2                 ;  弗雷德
3                    弗雷德
4                    ;弗雷德
6                   弗雷德< br> 7                  弗雷德
8                 弗雷德
11 ;               Fred

我需要一种有效的方法来传递参数[ 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

使用JOIN的解决方案：

select min(r1.NumberTaken) + 1
from MyTable r1
left outer join MyTable r2 on r2.NumberTaken = r1.NumberTaken + 1
where r1.NumberTaken >= 1 --your starting number
    and r2.NumberTaken is null

Answer 2

我将我的表称为“空白”，并使用了以下内容：

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

Answer 3

尝试基于集合的方法 - 应该更快

select min(t1.NumberTaken)+1 as "min_missing" from t t1
where not exists (select 1 from t t2 
                   where t1.NumberTaken = t2.NumberTaken+1)
and t1.NumberTaken > @StartingNumber

这是 Sybase 语法，因此如果需要，可以对 SQL 服务器消耗进行按摩。

Answer 4

创建一个临时表，其中包含从 StartValue 到 EndValue 的所有数字，并通过 LEFT OUTER JOIN 连接到数据表。