SQL：在表中查找丢失的 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1389605/
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
SQL: find missing IDs in a table
提问by Roee Adler
I have table with a unique auto-incremental primary key. Over time, entries may be deleted from the table, so there are "holes" in this field's values. For example, table data may be as follows:
我有一个带有唯一自动增量主键的表。随着时间的推移，条目可能会从表中删除，因此该字段的值中存在“漏洞”。例如，表数据可能如下：
ID | Value | More fields...
---------------------------------
2 | Cat | ...
3 | Fish | ...
6 | Dog | ...
7 | Aardvark | ...
9 | Owl | ...
10 | Pig | ...
11 | Badger | ...
15 | Mongoose | ...
19 | Ferret | ...
I'm interested in a query that will return the list of missing IDs in the table. For the data above, the expected results are:
我对返回表中缺失 ID 列表的查询感兴趣。对于上面的数据，预期的结果是：
ID
----
1
4
5
8
12
13
14
16
17
18
Notes:
笔记：
- It is assumed that the initial first ID was 1
- The maximum ID that should be examined is the final one, i.e. it's okay to assume that there were no additional entries after the current last one (see additional data on this point below)
- 假设初始的第一个 ID 是 1
- 应该检查的最大 ID 是最后一个，即可以假设在当前最后一个之后没有其他条目（请参阅下面有关这一点的其他数据）
A drawback of the above requirements is that the list will not return IDs that were created after ID 19 and that were deleted. I'm currently solving this case in code, because I hold the max ID created. However, if the query can take as a parameter MaxID, and also return those IDs between the current max and MaxID, that would be a nice "bonus" (but certainly not a must).
上述要求的一个缺点是该列表不会返回在 ID 19 之后创建并被删除的 ID。我目前正在用代码解决这个案例，因为我持有创建的最大 ID。但是，如果查询可以将 MaxID 作为参数，并返回当前 max 和 MaxID 之间的那些 ID，那将是一个不错的“奖励”（但肯定不是必须的）。
I'm currently working with MySQL, but consider moving to SQL Server, so I would like the query to fit both. Also, if you are using anything that can't run on SQLite, please mention it, thanks.
我目前正在使用 MySQL，但考虑转移到 SQL Server，所以我希望查询适合两者。另外，如果您正在使用任何无法在 SQLite 上运行的东西，请提及它，谢谢。
采纳答案by Eric
This question often comes up, and sadly, the most common (and most portable) answer is to create a temporary table to hold the IDs that shouldbe there, and do a left join. The syntax is pretty similar between MySQL and SQL Server. The only real difference is the temporary tables syntax.
这个问题经常出现，遗憾的是，最常见（也是最便携）的答案是创建一个临时表来保存应该在那里的 ID ，然后进行左连接。MySQL 和 SQL Server 的语法非常相似。唯一真正的区别是临时表语法。
In MySQL:
在 MySQL 中：
declare @id int
declare @maxid int
set @id = 1
select @maxid = max(id) from tbl
create temporary table IDSeq
(
id int
)
while @id < @maxid
begin
insert into IDSeq values(@id)
set @id = @id + 1
end
select
s.id
from
idseq s
left join tbl t on
s.id = t.id
where t.id is null
drop table IDSeq
In SQL Server:
在 SQL Server 中：
declare @id int
declare @maxid int
set @id = 1
select @maxid = max(id) from tbl
create table #IDSeq
(
id int
)
while @id < @maxid --whatever you max is
begin
insert into #IDSeq values(@id)
set @id = @id + 1
end
select
s.id
from
#idseq s
left join tbl t on
s.id = t.id
where t.id is null
drop table #IDSeq
回答by Nik Burns
I landed on this page hoping to find a solution for SQLITE as this was the only answer I found when searching for this same question for SQLITE.
我登陆此页面希望找到 SQLITE 的解决方案，因为这是我在为 SQLITE 搜索相同问题时找到的唯一答案。
The final solution I found was from this article here Float Middle Blog - SQLITE answer
我找到的最终解决方案来自这篇文章 Float Middle Blog - SQLITE answer
Hope it helps someone else out :-)
希望它可以帮助其他人:-)
the simple solution being:
简单的解决方案是：
SELECT DISTINCT id +1
FROM mytable
WHERE id + 1 NOT IN (SELECT DISTINCT id FROM mytable);
genius.
天才。
回答by Pavlo Svirin
Here's the query for SQL Server:
这是 SQL Server 的查询：
;WITH Missing (missnum, maxid)
AS
(
SELECT 1 AS missnum, (select max(id) from @TT)
UNION ALL
SELECT missnum + 1, maxid FROM Missing
WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN @TT tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0);
Hope this is helpful.
希望这是有帮助的。
回答by Per L?fgren
I know it's an old question and already has an accepted answer, but using a temp table isn't really necessary. Fixed formatting (sorry for double post).
我知道这是一个老问题，并且已经有一个公认的答案，但实际上并没有必要使用临时表。固定格式（对不起，双重发布）。
DECLARE @TEST_ID integer, @LAST_ID integer, @ID integer
SET @TEST_ID = 1 -- start compare with this ID
SET @LAST_ID = 100 -- end compare with this ID
WHILE @TEST_ID <= @LAST_ID
BEGIN
SELECT @ID = (SELECT <column> FROM <table> WHERE <column> = @TEST_ID)
IF @ID IS NULL
BEGIN
PRINT 'Missing ID: ' + CAST(@TEST_ID AS VARCHAR(10))
END
SET @TEST_ID = @TEST_ID + 1
END
回答by Sev
PostgreSQL-only, inspired by other answers here.
仅限 PostgreSQL，受此处其他答案的启发。
SELECT all_ids AS missing_ids
FROM generate_series((SELECT MIN(id) FROM your_table), (SELECT MAX(id) FROM your_table)) all_ids
EXCEPT
SELECT id FROM your_table
回答by EvilTeach
This is an Oracle only solution. It doesn't address the full question, but is left here for others that may be using Oracle.
这是 Oracle 唯一的解决方案。它没有解决完整的问题，但留在这里供其他可能使用 Oracle 的人使用。
select level id -- generate 1 .. 19
from dual
connect by level <= 19
minus -- remove from that set
select id -- everything that is currently in the
from table -- actual table
回答by sovan
The single query can find the missing IDs..
单个查询可以找到丢失的 ID..
SELECT distinct number
FROM master..spt_values
WHERE number BETWEEN 1 and (SELECT max(id) FROM MyTable)
AND number NOT IN (SELECT id FROM MyTable)
回答by Kiran.Bakwad
to get the missing rows from table
从表中获取缺失的行
DECLARE @MaxID INT = (SELECT MAX(ID) FROM TABLE1)
SELECT SeqID AS MissingSeqID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY column_id) SeqID from sys.columns) LkUp
LEFT JOIN dbo.TABLE1 t ON t.ID = LkUp.SeqID
WHERE t.ID is null and SeqID < @MaxID
回答by piyush
SELECT DISTINCT id -1
FROM users
WHERE id != 1 AND id - 1 NOT IN (SELECT DISTINCT id FROM users)
Explanation:( id - 1 )..... checking for any previous id present in table
说明：(id - 1 ..... 检查表中存在的任何先前的 id
( id != 1 ).....neglecting when current id is 1 as its previous id will be 0 zero.
( id != 1 ..... 当当前 id 为 1 时忽略，因为其先前的 id 将为 0 零。
回答by àlex Resta
Easiest solution for me: Create a select that gives all ids up to max sequence value (ex:1000000), and filter:
对我来说最简单的解决方案：创建一个选择，使所有 id 达到最大序列值（例如：1000000），然后过滤：
with listids as (
Select Rownum idnumber From dual Connect By Rownum <= 1000000)
select * from listids
where idnumber not in (select id from table where id <=1000000)
