问题描述
我们有一个数据库，其中的表的值是从另一个系统导入的.有自增列，没有重复值，但有缺失值.例如，运行此查询:
We have a database with a table whose values were imported from another system. There is an auto-increment column, and there are no duplicate values, but there are missing values. For example, running this query:
select count(id) from arrc_vouchers where id between 1 and 100
应该返回 100，但它返回 87.我可以运行任何查询来返回缺失数字的值吗?例如，可能存在 id 1-70 和 83-100 的记录，但没有 id 为 71-82 的记录.我想返回 71、72、73 等
should return 100, but it returns 87 instead. Is there any query I can run that will return the values of the missing numbers? For example, the records may exist for id 1-70 and 83-100, but there are no records with id's of 71-82. I want to return 71, 72, 73, etc.
这可能吗?
推荐答案
更新
ConfexianMJS 在性能方面提供了更好答案.
以下版本适用于任何大小的表格(不仅仅是 100 行):
Here's version that works on table of any size (not just on 100 rows):
SELECT (t1.id + 1) as gap_starts_at,
(SELECT MIN(t3.id) -1 FROM arrc_vouchers t3 WHERE t3.id > t1.id) as gap_ends_at
FROM arrc_vouchers t1
WHERE NOT EXISTS (SELECT t2.id FROM arrc_vouchers t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL
gap_starts_at- 当前间隙中的第一个 idgap_ends_at- 当前间隙中的最后一个 idgap_starts_at- first id in current gapgap_ends_at- last id in current gap
这篇关于如何在mysql中找到顺序编号的差距?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！