查找 SQL Server 中最小的未使用数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/684106/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find the smallest unused number in SQL Server
提问by Michael La Voie
How do you find the smallest unused number in a SQL Server column?
如何在 SQL Server 列中找到最小的未使用数字？
I am about to import a large number of manually recorded records from Excel into a SQL Server table. They all have a numeric ID (called document number), but they weren't assigned sequentially for reasons that no longer apply, meaning from now on when my web site records a new record, it needs to assign it the smallest possible document number (greater than zero) that has not already been taken.
我即将从 Excel 导入大量手动记录的记录到 SQL Server 表中。它们都有一个数字 ID（称为文档编号），但由于不再适用的原因，它们没有按顺序分配，这意味着从现在开始，当我的网站记录新记录时，需要为其分配尽可能小的文档编号（大于零）尚未被采用。
Is there a way to do this through plain SQL or is this a problem for TSQL/code?
有没有办法通过普通 SQL 来做到这一点，或者这是 TSQL/代码的问题？
Thanks!
谢谢！
EDIT
编辑
Special thanks to WWfor raising the issue of concurrency. Given that this is a web app, it is multi-threaded by definition and anyone faced with this same problem should consider either a code or DB level lock to prevent a conflict.
特别感谢WW提出并发问题。鉴于这是一个 Web 应用程序，它根据定义是多线程的，任何面临同样问题的人都应该考虑使用代码或数据库级锁来防止冲突。
LINQ
LINQ
FYI - this can be accomplished via LINQ with the following code:
仅供参考 - 这可以通过 LINQ 使用以下代码完成：
var nums = new [] { 1,2,3,4,6,7,9,10};
int nextNewNum = (
from n in nums
where !nums.Select(nu => nu).Contains(n + 1)
orderby n
select n + 1
).First();
nextNewNum == 5
nextNewNum == 5
回答by Darrel Miller
Find the first row where there does not exist a row with Id + 1
查找不存在 Id + 1 行的第一行
SELECT TOP 1 t1.Id+1
FROM table t1
WHERE NOT EXISTS(SELECT * FROM table t2 WHERE t2.Id = t1.Id + 1)
ORDER BY t1.Id
Edit:
编辑：
To handle the special case where the lowest existing id is not 1, here is a ugly solution:
为了处理最低现有 id 不是 1 的特殊情况，这是一个丑陋的解决方案：
SELECT TOP 1 * FROM (
SELECT t1.Id+1 AS Id
FROM table t1
WHERE NOT EXISTS(SELECT * FROM table t2 WHERE t2.Id = t1.Id + 1 )
UNION
SELECT 1 AS Id
WHERE NOT EXISTS (SELECT * FROM table t3 WHERE t3.Id = 1)) ot
ORDER BY 1
回答by MarkusQ
If you sort them by numeric ID, the number you are looking for will be the first one for which the ROW_NUMBER() function doesn't equal the ID.
如果按数字 ID 对它们进行排序，则您要查找的数字将是第一个 ROW_NUMBER() 函数不等于 ID 的数字。
回答by WW.
No mention of locking or concurrency in any of the answers so far.
到目前为止，在任何答案中都没有提到锁定或并发性。
Consider these two users adding a document at nearly the same time:-
考虑这两个用户几乎同时添加文档：-
User 1 User 2
Find Id
Find Id
Id = 42
Id = 42
Insert (42..)
Insert (42..)
Error!
You either need to: a) Handle that error and go around the loop again looking for the next available Id, OR b) Take a lock out at the start of the process so only 1 user is looking for Ids at a particular time
您要么需要：a) 处理该错误并再次循环查找下一个可用的 Id，或者 b) 在流程开始时锁定，以便在特定时间只有 1 个用户正在查找 Id
回答by Bill Karwin
SELECT TOP 1 t1.id+1
FROM mytable t1
LEFT OUTER JOIN mytable t2 ON (t1.id + 1 = t2.id)
WHERE t2.id IS NULL
ORDER BY t1.id;
This is an alternative to the answers using correlated subqueries given by @Jeffrey Hantlin and @Darrel Miller.
这是使用@Jeffrey Hantlin 和@Darrel Miller 给出的相关子查询的答案的替代方法。
However, the policy you're describing is really not a good idea. ID values should be unique, but should not be required to be consecutive.
但是，您所描述的政策确实不是一个好主意。ID 值应该是唯一的，但不应要求是连续的。
What happens if you email someone with a link to document #42, and then subsequently delete the document? Later, you re-use the id #42 for a new document. Now the recipient of the email will follow the link to the wrong document!
如果您通过电子邮件向某人发送文档 #42 的链接，然后删除该文档，会发生什么情况？稍后，您将 id #42 重新用于新文档。现在电子邮件的收件人将按照链接指向错误的文档！
回答by user6586
declare @value int
select @value = case
when @value is null or @value + 1 = idcolumn
then idcolumn
else @value end
from table
order by idcolumn
select @value + 1
Does 1 table scan rather than 2 scans a hash match and a join like the top answer
是否进行 1 次表扫描而不是 2 次扫描哈希匹配和连接，如最佳答案
回答by Jeffrey Hantin
If there are gaps in the sequence, you can find the first gap with something like this:
如果序列中有间隙，您可以使用以下内容找到第一个间隙：
select top 1 (found.id + 1) nextid from (select id from items union select 0) found
where not exists (select * from items blocking
where blocking.id = found.id + 1)
order by nextid asc
In other words, find the least ID whose successor does not exist, and return that successor. If there are no gaps, it returns one greater than the greatest extant ID. A placeholder ID of 0 is inserted to insure that IDs starting with 1 are considered.
换句话说，找到后继不存在的最小ID，并返回该后继。如果没有间隙，它返回一个大于最大现存 ID 的值。插入 0 的占位符 ID 以确保考虑以 1 开头的 ID。
Note that this will take at least n log n time.
请注意，这至少需要 n log n 时间。
Microsoft SQL permits the use of a fromclause in an insertstatement, so you may not need to resort to procedural code.
Microsoft SQL 允许from在insert语句中使用子句，因此您可能不需要求助于过程代码。
回答by Matt Grande
Is there a reason that it has to be the smallest possible number? Why do you need to fill the holes?
有没有理由它必须是最小的数字？为什么需要填坑？
Editto ad the answer, since it's a business rule.
编辑以添加答案，因为它是业务规则。
DECLARE @counter int
DECLARE @max
SET @counter = 0
SET @max = SELECT MAX(Id) FROM YourTable
WHILE @counter <= @max
BEGIN
SET @counter = @counter + 1
IF NOT EXISTS (SELECT Id FROM YourTable WHERE Id = @counter)
BREAK
END
END
(I don't have a db handy, so this may not be 100% accurate, but you should be able to get it from there)
（我手边没有数据库，所以这可能不是 100% 准确，但你应该能够从那里得到它）
回答by Rich Garrett
Here is a simple approach. It may no be fast. It will not find missing numbers at the beginning.
这是一个简单的方法。可能不快。它不会在开头找到丢失的数字。
SELECT MIN(MT1.MyInt+1)
FROM MyTable MT1
LEFT OUTER JOIN MyTable MT2 ON (MT1.MyInt+1)=MT2.MyInt
WHERE MT2.MyInt Is Null
回答by Rich Garrett
select
MIN(NextID) NextUsableID
from (
select (case when c1 = c2 then 0
else c1 end) NextID
from ( select ROW_NUMBER() over (order by record_id) c1,
record_id c2
from myTable)
)
where NextID > 0
回答by maf-soft
Let's assume your IDs should always start with 1:
假设您的 ID 应始终以 1 开头：
SELECT MIN(a.id) + 1 AS firstfree
FROM (SELECT id FROM table UNION SELECT 0) a
LEFT JOIN table b ON b.id = a.id + 1
WHERE b.id IS NULL
This handles all cases I can think of - including no existing records at all.
这可以处理我能想到的所有情况 - 包括根本没有现有记录。
The only thing I don't like about this solution is that additional conditions have to be included twice, like that:
我唯一不喜欢这个解决方案的是附加条件必须包含两次，如下所示：
SELECT MIN(a.id) + 1 AS firstfree
FROM (SELECT id FROM table WHERE column = 4711 UNION SELECT 0) a
LEFT JOIN table b ON b.column = 4711 AND b.id = a.id + 1
WHERE b.id IS NULL
Please also notice the comments about locking and concurrency - the requirement to fill gaps is in most cases bad design and can cause problems. However, Ihad a good reason to do it: the IDs are to be printed and typed by humans and we don't want to have IDs with many digits after some time, while all the low ones are free...
还请注意关于锁定和并发性的评论 - 在大多数情况下，填补空白的要求是糟糕的设计，可能会导致问题。但是，我有一个很好的理由这样做：ID 是由人工打印和输入的，我们不想在一段时间后拥有多位数的 ID，而所有低位的 ID 都是免费的...
