sql查询以获取已删除的记录 [英] sql query to get deleted records

问题描述

您有一个表table1，其中包含id列，即int(11), not null, auto_increment，从1开始.

You have a table table1 that contains id column, that is int(11), not null, auto_increment and starts from 1.

假设您有10,000条记录.很明显，最后一条记录的ID是10,000. 删除3条记录后，表中将有9,997条记录，但是最后一条记录的ID值仍为10,000(如果未删除最后一条记录).

Suppose, you have 10,000 records. It is clear the id of the last record is 10,000. Once you removed 3 records, you have 9,997 records in the table, but the last record id value is still 10,000 (if the last record was not deleted).

如何显示使用1个sql查询已删除的记录?

How to display what records have been removed using 1 sql query?

谢谢.

推荐答案

我认为最简单的方法是创建一个仅包含ID的虚拟/临时表. 1-1000然后左联接到该表.

I think easiest would be to have a dummy/temp table with just ids. 1-1000 then left join to that table.

但是一旦完成，请确保从虚拟/临时表中删除已删除"记录.否则，它们将每次出现.

But be sure to remove the "deleted" records from your dummy/temp table once you're done. Otherwise, they will show up every time.

>>编辑<< 您可以进行自我加入以找出是否缺少ID....

>> EDIT << You can do self join to figure out if you're missing ids....

select a.id + 1 MissingIds
from <table> a
left join <table> b
  on a.id = b.id - 1
where b.id is null
  and a.id < 10000

这篇关于sql查询以获取已删除的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！