SQL：在表中查找丢失的ID

Question

我的表具有唯一的自动增量主键。随着时间的流逝，条目可能会从表中删除，因此此字段的值中存在“漏洞”。例如，表数据可能如下： ID  | Value    | More fields...--------------------------------- 2   | Cat      | ...  3   | Fish     | ... 6   | Dog      | ... 7   | Aardvark | ... 9   | Owl      | ... 10  | Pig      | ... 11  | Badger   | ... 15  | Mongoose | ... 19  | Ferret   | ...我对查询将返回表中缺少的ID的列表感兴趣。对于以上数据，预期结果为： ID ---- 1 4 5 8 12 13 14 16 17 18笔记：假设最初的第一个ID为1应该检查的最大ID是最后一个ID，即可以假设当前的最后一个ID之后没有其他条目（请参阅以下有关这一点的其他数据）上述要求的缺点是该列表将不会返回ID 19之后创建的ID和已删除的ID。我目前正在用代码解决这种情况，因为我持有创建的最大ID。但是，如果查询可以将MaxID作为参数，并且还返回当前max和MaxID之间的那些ID，那将是一个不错的“奖励”（但肯定不是必须的）。我目前正在使用MySQL，但是考虑迁移到SQL Server，因此我希望查询能够同时适合这两者。另外，如果您使用的是无法在SQLite上运行的工具，请多谢。

Answer 1

这个问题经常出现，可悲的是，最常见（也是最可移植的）答案是创建一个临时表来保存应该存在的ID ，然后进行左联接。MySQL和SQL Server之间的语法非常相似。唯一真正的区别是临时表语法。

在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

在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

Answer 2

我登陆此页面希望找到SQLITE的解决方案，因为这是我在搜索相同问题的SQLITE时找到的唯一答案。

我找到的最终解决方案是来自本文的 浮动中间博客-SQLITE答案

希望它可以帮助别人:-)

简单的解决方案是：

SELECT DISTINCT id +1

FROM mytable

WHERE id + 1 NOT IN (SELECT DISTINCT id FROM mytable);

天才。