sql - 如何从表中获取第一个免费 ID

Question

我有一个带有主键字段 ID 的表。我不想使用身份，因为我需要给用户手动选择新对象的 ID 的可能性。所以我的想法是:

默认情况下，在编辑 View 中，ID 字段将为 0。

如果用户不更改它，我需要找到第一个免费 ID 并使用它。

如果用户更改了 ID，我首先需要检查是否有另一个具有该 ID 的对象，在这种情况下会抛出错误。

如果没有，请使用用户选择的 ID。

创建新对象

问题是如何查询和 SQL Server 表中的第一个免费 ID 号？

示例 1:

ID
--
1
2
10

第一个免费 ID 是 3

示例 2:

ID
--
1
2
3
4

第一个免费 ID 是 5

有没有办法做到这一点？
我能想到的就是获取最小值和最大值，为可能的值创建一个循环，然后与表数据进行比较，但它涉及对数据库的太多查询。
谢谢!

Answer 1

您可以找到第一个空闲 ID 作为没有“下一个”值的第一个 ID:

select coalesce(min(t.id) + 1, 0)
from table t left outer join
     table t2
     on t.id = t2.id - 1
where t2.id is null;

编辑:

如果要将“1”作为潜在的缺失值处理:

select (case when min(minid) > 1 then 1 else coalesce(min(t.id) + 1, 0) end)
from table t left outer join
     table t2
     on t.id = t2.id - 1 cross join
     (select min(id) as minid from table t) const
where t2.id is null;