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 intdeclare @maxid intset @id = 1select @maxid = max(id) from tblcreate temporary table IDSeq(&nbsp; &nbsp; id int)while @id < @maxidbegin&nbsp; &nbsp; insert into IDSeq values(@id)&nbsp; &nbsp; set @id = @id + 1endselect&nbsp;&nbsp; &nbsp; s.id&nbsp;from&nbsp;&nbsp; &nbsp; idseq s&nbsp;&nbsp; &nbsp; left join tbl t on&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; s.id = t.id&nbsp;&nbsp;where t.id is null&nbsp;drop table IDSeq在SQL Server中：declare @id intdeclare @maxid intset @id = 1select @maxid = max(id) from tblcreate table #IDSeq(&nbsp; &nbsp; id int)while @id < @maxid --whatever you max isbegin&nbsp; &nbsp; insert into #IDSeq values(@id)&nbsp; &nbsp; set @id = @id + 1endselect&nbsp;&nbsp; &nbsp; s.id&nbsp;from&nbsp;&nbsp; &nbsp; #idseq s&nbsp;&nbsp; &nbsp; left join tbl t on&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; s.id = t.id&nbsp;&nbsp;where t.id is null&nbsp;drop table #IDSeq

Answer 2

我登陆此页面希望找到SQLITE的解决方案，因为这是我在搜索相同问题的SQLITE时找到的唯一答案。我找到的最终解决方案是来自本文的 浮动中间博客-SQLITE答案希望它可以帮助别人:-)简单的解决方案是：SELECT DISTINCT id +1FROM mytableWHERE id + 1 NOT IN (SELECT DISTINCT id FROM mytable);天才。