SQL: 查找表中缺少的 ID

我有一个唯一的自动增量主键表。随着时间的推移，条目可能会从表中删除，因此该字段的值中存在“漏洞”。例如，表格数据可能如下:

 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

备注:

  1. 假设初始的第一个 ID 是1
  2. 应该检查的最大 ID 是最后一个 ID，也就是说，可以假定在当前最后一个 ID 之后没有其他条目(请参阅下面关于这一点的附加数据)

上述要求的一个缺点是，列表不会返回在 ID19之后创建并删除的 ID。我目前正在用代码解决这个问题，因为我持有创建的最大 ID。但是，如果查询可以将 MaxID 作为参数，并返回当前 max 和 MaxID 之间的那些 ID，那将是一个不错的“额外收获”(但肯定不是必须的)。

我目前正在使用 MySQL，但是考虑迁移到 SQLServer，所以我希望查询能够同时满足这两个要求。此外，如果您正在使用任何不能在 SQLite 上运行的东西，请提及它，谢谢。

73228 次浏览
小开
最佳答案

这个问题经常出现，遗憾的是，最常见的(也是最可移植的)答案是创建一个临时表来保存存在 应该的 ID，并执行左连接。MySQL 和 SQLServer 的语法非常相似。唯一真正的区别是临时表的语法。

在 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

在 SQLServer 中:

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
小开

以下是对 SQLServer 的查询:

;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);

希望这对你有帮助。

小开

这是甲骨文独有的解决方案。它并没有解决完整的问题，而是留给其他可能正在使用 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
小开

我知道这是个老问题，而且已经有了可以接受的答案, 但是使用临时表并不是必须的。

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
小开

单个查询可以找到丢失的 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)
小开

更新: 这个方法花费的时间太长，所以我编写了一个 linux 命令来查找文本文件中的空白。它以相反的顺序执行，因此首先将所有 id 转储到一个文本文件中，如下所示;

nohup mysql --password=xx -e 'select id from tablename order by id desc' databasename > /home/ids.txt &

前两行和最后两行只是为了记录花了多长时间。150万个 ID (差不多)花了我57秒，这是在一个缓慢的服务器上。在 i 中设置最大 ID，然后就可以了。

T="$(date +%s)"; \
i=1574115; \
while read line; do \
if  [[ "$line" != "$i" ]] ; then \
if [[ $i -lt 1 ]] ; then break; fi; \
if  [[ $line -gt 1 ]] ; then \
missingsequenceend=$(( $line + 1 )); \
minusstr="-"; \
missingsequence="$missingsequenceend$minusstr$i"; \
expectnext=$(( $line - 1 )); \
i=$expectnext; \
echo -e "$missingsequence"; \
fi; \
else \
i=$(( $i - 1 )); \
fi; \
done \
< /home/ids.txt; \
T="$(($(date +%s)-T))"; \
echo "Time in seconds: ${T}"

输出示例:

1494505-1494507
47566-47572
Time in seconds: 57

此外，Eric 的答案中的代码也出现了语法错误，但是在更改分隔符、在适当的位置使用分号并将其存储在过程中之后，它就可以工作了。

确保您设置了正确的 max ID、数据库名称和表名称(在选择查询中)。如果要更改过程名称，请在所有3个位置都进行更改。

use dbname;
drop procedure if exists dorepeat;
delimiter #
CREATE PROCEDURE dorepeat()
BEGIN
set @id = 1;
set @maxid = 1573736;
drop table if exists IDSeq;
create temporary table IDSeq
(
id int
);


WHILE @id < @maxid DO
insert into IDSeq values(@id);
set @id = @id + 1;
END WHILE;


select
s.id
from
IDSeq s
left join tablename t on
s.id = t.id
where t.id is null;


drop table if exists IDSeq;


END#
delimiter ;
CALL dorepeat;

我还在其他地方找到了这个查询，但是我还没有测试它。

SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
FROM tablename AS a, tablename AS b
WHERE a.id < b.id
GROUP BY a.id
HAVING start < MIN(b.id)
小开

这个问题只需要一个查询就可以解决

select lft.id + 1 as missing_ids
from tbl as lft left outer join tbl as rght on lft.id + 1 = rght.id
where rght.id is null and lft.id between 1 and (Select max(id)-1 from tbl)

在 Mysql 上测试

小开

试试 MySQL

DELIMITER ||
DROP PROCEDURE IF EXISTS proc_missing ||
CREATE PROCEDURE proc_missing()
BEGIN
SET @minID = (SELECT MIN(`id`) FROM `tbl_name` WHERE `user_id`=13);
SET @maxID = (SELECT MAX(`id`) FROM `tbl_name` WHERE `user_id`=13);
REPEAT
SET @tableID = (SELECT `id` FROM `tbl_name` WHERE `id` = @minID);
IF (@tableID IS NULL) THEN
INSERT INTO temp_missing SET `missing_id` = @tableID;
END IF;
SET @minID = @minID + 1;
UNTIL(@minID <= @maxID)
END REPEAT;
END ||
DELIMITER ;
小开

请尝试此查询。这一个查询就足以得到缺少的数字: (请将 TABLE _ NAME 替换为您正在使用的表名)

select sno as missing from(SELECT @row := @row + 1 as sno FROM
(select 0 union all select 1 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 6 union all select 7 union all
select 8 union all select 9) t,(select 0 union all select 1 union all select 3
union all select 4 union all select 5 union all select 6 union all select 6
union all select 7 union all select 8 union all select 9) t2,(select 0
union all select 1 union all select 3 union all select 4 union all select 5
union all select 6 union all select 6 union all select 7 union all select 8
union all select 9) t3, (select 0 union all select 1 union all select 3 union
all select 4 union all select 5 union all select 6 union all select 6 union all
select 7 union all select 8 union all select 9) t4,
(SELECT @row:=0) as b where @row<1000) as a where a.sno  not in
(select distinct b.no from
(select b.*,if(@mn=0,@mn:=b.no,@mn) as min,(@mx:=b.no) as max from
(select ID as no from TABLE_NAME as a) as b,
(select @mn:=0,@mx:=0) as x order by no) as b) and
a.sno between @mn and @mx;
小开

这就是我用来查找一个名为 tablename 的表丢失的 id 的方法

从表名 a 中选择 a.id + 1 miss _ ID 其中 a.id + 1不在(从表名 b 中选择 id，其中 b.id = a.id + 1) 和 a.id! = (通过 id desc limit 1从表名 c 顺序中选择 id)

它会返回丢失的身份信息。 如果有两个(2)或更多的连续丢失 id，它将只返回第一个 id。

小开

几天前，我正在写一份生产报告，发现一些数字不见了。丢失的数字非常重要，所以我被要求找到所有丢失数字的列表，以便调查。我在这里发布了一个博客条目，包括一个完整的演示，其中包括一个脚本，用于在一个示例表中查找缺失的数字/ID。

建议的脚本相当长，所以我不会在这里包括它:

  1. 创建一个临时表并存储所有不同的数字。
  2. 查找前面缺少内容的 NextID。存储到一个 TempTable 中。
  3. 创建一个临时表来存储缺少的数字详细信息。
  4. 使用 WHILE 循环开始查找丢失的 id。
  5. 从 # MissingID 临时表中选择缺少的数据。
小开

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

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

希望这能帮到别人: -)

简单的解决办法是:

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

天才。

小开

将 SQL CTE (来自 Paul Svirin)转换为 Oracle 版本，如下所示(用表名替换 YOURTABLE) :

WITH Missing (missnum,maxid) as (
SELECT 1 missnum, (select max(id) from :YOURTABLE) maxid from dual
UNION ALL
SELECT m.missnum + 1,m.maxid
FROM Missing m
WHERE m.missnum < m.maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN :YOURTABLE tt on tt.id = Missing.missnum
WHERE tt.id is NULL
小开

使用 @PaulSvirin的答案，我用一个 UNION展开它，以显示表中的所有数据，包括使用 NULL丢失的记录。

WITH Missing(missnum, maxid) AS
(SELECT (SELECT MIN(tmMIN.TETmeetingID)
FROM tblTETMeeting AS tmMIN)
AS missnum,
(SELECT MAX(tmMAX.TETmeetingID)
FROM tblTETMeeting AS tmMAX)
AS maxid
UNION ALL
SELECT missnum + 1, maxid
FROM Missing
WHERE missnum < maxid)
SELECT missnum AS TETmeetingID,
tt.DateID,
tt.WeekNo,
tt.TETID
FROM Missing LEFT JOIN tblTETMeeting tt ON tt.TETmeetingID = Missing.missnum
WHERE tt.TETmeetingID IS NULL
UNION
SELECT tt.TETmeetingID,
tt.DateID,
tt.WeekNo,
tt.TETID
FROM tblTETMeeting AS tt
OPTION ( MAXRECURSION 0 )

工作很好！

TETmeetingID    DateID  WeekNo  TETID
29  3063    21  1
30  null    null    null
31  null    null    null
32  null    null    null
33  null    null    null
34  3070    22  1
35  3073    23  1
小开

PostgreSQL-only，灵感来自这里的其他答案。

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
小开

从表中获取缺少的行

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
小开 
SELECT DISTINCT id -1
FROM users
WHERE id != 1 AND id - 1 NOT IN (SELECT DISTINCT id FROM users)

说明: (id-1) ... . . 检查表中以前的 id

(id! = 1) ... ... 忽略当前 id 为1时，因为它的前一个 id 将为0。

小开

对我来说最简单的解决方案: 创建一个选择，给所有 id 最大序列值(ex: 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)
小开

我刚刚找到了解决 Postgres 问题的方法:

select min(gs)
from generate_series(1, 1999) as gs
where gs not in (select id from mytable)
小开

借用@Eric 建议的修改版本。这是用于 SQLServer 的，并在临时表中保存缺少范围的开始值和结束值。如果间隙只是一个值，则将 NULL作为结束值，以便于可视化。

它会产生这样的输出

|StartId| EndId |
|-------|-------|
|     1 | 10182 |
| 10189 | NULL  |
| 10246 | 15000 |

这是需要用表和标识列替换 myTable和 id的脚本。

declare @id bigint
declare @endId bigint
declare @maxid bigint
declare @previousid bigint=0


set @id = 1
select @maxid = max(id) from myTable


create table #IDGaps
(
startId bigint,
endId bigint
)


while @id < @maxid
begin
if NOT EXISTS(select id from myTable where id=@id)
BEGIN
SET @previousid=@id
select top 1 @endId=id from myTable where id>@id


IF @id=@endId-1
insert into #IDGaps values(@id,null)
ELSE
insert into #IDGaps values(@id,@endId-1)


SET @id=@endId
        

END
ELSE
set @id = @id + 1
end


select * from #IDGaps


drop table #IDGaps
小开

我有一个很大的审计表，需要一些东西，运行迅速-这对我来说很好。它合并缺少范围的顶部和底部 ID

select minQ.num,minId,maxId from


(SELECT DISTINCT id +1 as minId, Row_Number() Over ( Order By id ) As Num
FROM tblAuditLoghistory
WHERE id + 1 NOT IN (SELECT DISTINCT id FROM tblAuditLogHistory)
AND id < (SELECT max(id) FROM tblAuditLoghistory)) Minq
join




(SELECT DISTINCT id - 1 as maxId, Row_Number() Over ( Order By id ) As Num
FROM tblAuditLoghistory
WHERE id - 1 NOT IN (SELECT DISTINCT id FROM tblAuditLogHistory)
AND id > (SELECT min(id) FROM tblAuditLoghistory)) maxQ on minQ.num=maxQ.num
小开

SQLITE 的解决方案

如果表 id 只支持正值，则可以使用

SELECT DISTINCT table_id - 1 AS next_id
FROM table
WHERE next_id NOT IN (SELECT DISTINCT table_id FROM table)
AND next_id > 0

否则，应删除大于最大 id 的 id

SELECT DISTINCT table_id + 1 AS next_id
FROM table
WHERE next_id NOT IN (SELECT DISTINCT table_id FROM table)
AND id < (SELECT MAX(id) FROM table)

提交答案