前言#
在数据清洗/排查时，我们经常需要找出某个表里本应连续递增的主键/编号字段中，哪些数字“断号”了。
举个例子：people.id 从 1 开始递增，但中间有人删过数据，于是出现缺失。我们希望用一条 SQL直接查出缺失编号：
缺失 ID：4, 5, 6, 14, 20, 21, 22, 23, 24
构造数据#
下面我就以Oracle 19c版本数据库为例子。创建一个名为people的表。
-- 创建people表
create table people
(
id number primary key,
name varchar2(20),
id_card_number varchar2(18),
sex varchar2(10),
birthday date,
companyname varchar2(200),
phone number(11,0),
email varchar2(200)
);
-- 设置日期格式
alter session set nls_date_format='YYYY-MM-DD';
-- 插入数据
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(1,'程向欢','998903297003105622','男','1989-08-20','吉林本健生物技术股份有限公司',17562635371,'yuanmingze787@gmail.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(2,'傅永','263329186803313901','女','1984-02-23','忻州易京文化传媒有限公司',13522672709,'kewei239@163.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(3,'黄宁','560555215710301609','女','1970-10-04','绵阳春升教育咨询有限公司',17864884535,'pangmei120@qq.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(7,'夏晴娴','792459186810104789','男','1981-05-28','鞍山四海友诚装饰品股份有限公司',14965784873,'xueyugang344@163.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(8,'苏小','894179182705302265','女','2020-05-17','白银茂长生物技术集团有限公司',13340063183,'dongwen117@qq.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(9,'郝希齐','358928183312203948','男','1970-05-24','广元娇建培训集团有限公司',15438982015,'daiyuanlan382@qq.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(10,'曲新','414835394001209712','女','1989-04-04','咸阳美威科技集团有限公司',14993593698,'tongrong880@gmail.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(11,'辛一诺','927866289504313248','女','2018-06-11','衢州晨鑫工程技术股份有限公司',18562767880,'peihua303@163.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(12,'白雨桐','61145121091031771X','女','1981-08-14','伊春健圆文化集团有限公司',19889214484,'huangmanting136@qq.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(13,'谷浩然','729462231101302467','女','1982-11-27','佳木斯凤尚医药科技股份有限公司',15749326665,'guoxinyi567@163.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(15,'龚欣怡','518439192212204497','男','1992-08-30','三亚诺拓物流运输股份有限公司',19809420459,'zhuangxiyuan919@qq.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(16,'孔宇航','231819194212042424','男','2002-09-17','乐山玉思电力设备集团有限公司',18372778598,'xiaoxiangguang761@gmail.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(17,'辛金','439897363807310147','女','1993-04-03','恩施土家族苗族德美商贸有限公司',14944062965,'zhaohaoran515@gmail.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(18,'乔美彩','160707220406209525','女','1976-05-29','平顶山京德园林绿化工程有限公司',13658208990,'maoxincan615@gmail.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(19,'卢俊','786514320010207528','女','2022-11-06','识娇婚庆服务股份有限公司',17526097533,'oulin042@qq.com');
insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(25,'屈勤诚','748381189111313308','女','2001-06-14','十堰尔飞机械设备集团有限公司',17569406333,'kongxinyao478@gmail.com');
commit;执行上述命令如下：
[oracle@19c ~]$ sqlplus user1/oracle@PDBPROD1
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 14 21:51:33 2026
Version 19.29.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Wed Jan 14 2026 21:18:37 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.29.0.0.0
USER1@PDBPROD1> create table people
(
id number primary key,
name varchar2(20),
id_card_number varchar2(18),
sex varchar2(10),
birthday date,
companyname varchar2(200),
phone number(11,0),
email varchar2(200)
); 2 3 4 5 6 7 8 9 10 11
Table created.
USER1@PDBPROD1> alter session set nls_date_format='YYYY-MM-DD';
Session altered.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(1,'程向欢','998903297003105622','男','1989-08-20','吉林本健生物技术股份有限公司',17562635371,'yuanmingze787@gmail.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(2,'傅永','263329186803313901','女','1984-02-23','忻州易京文化传媒有限公司',13522672709,'kewei239@163.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(3,'黄宁','560555215710301609','女','1970-10-04','绵阳春升教育咨询有限公司',17864884535,'pangmei120@qq.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(7,'夏晴娴','792459186810104789','男','1981-05-28','鞍山四海友诚装饰品股份有限公司',14965784873,'xueyugang344@163.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(8,'苏小','894179182705302265','女','2020-05-17','白银茂长生物技术集团有限公司',13340063183,'dongwen117@qq.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(9,'郝希齐','358928183312203948','男','1970-05-24','广元娇建培训集团有限公司',15438982015,'daiyuanlan382@qq.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(10,'曲新','414835394001209712','女','1989-04-04','咸阳美威科技集团有限公司',14993593698,'tongrong880@gmail.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(11,'辛一诺','927866289504313248','女','2018-06-11','衢州晨鑫工程技术股份有限公司',18562767880,'peihua303@163.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(12,'白雨桐','61145121091031771X','女','1981-08-14','伊春健圆文化集团有限公司',19889214484,'huangmanting136@qq.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(13,'谷浩然','729462231101302467','女','1982-11-27','佳木斯凤尚医药科技股份有限公司',15749326665,'guoxinyi567@163.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(15,'龚欣怡','518439192212204497','男','1992-08-30','三亚诺拓物流运输股份有限公司',19809420459,'zhuangxiyuan919@qq.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(16,'孔宇航','231819194212042424','男','2002-09-17','乐山玉思电力设备集团有限公司',18372778598,'xiaoxiangguang761@gmail.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(17,'辛金','439897363807310147','女','1993-04-03','恩施土家族苗族德美商贸有限公司',14944062965,'zhaohaoran515@gmail.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(18,'乔美彩','160707220406209525','女','1976-05-29','平顶山京德园林绿化工程有限公司',13658208990,'maoxincan615@gmail.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(19,'卢俊','786514320010207528','女','2022-11-06','识娇婚庆服务股份有限公司',17526097533,'oulin042@qq.com');
1 row created.
USER1@PDBPROD1> insert into people(id,name,id_card_number,sex,birthday,companyname,phone,email) values(25,'屈勤诚','748381189111313308','女','2001-06-14','十堰尔飞机械设备集团有限公司',17569406333,'kongxinyao478@gmail.com');
1 row created.
USER1@PDBPROD1> commit;
Commit complete.
USER1@PDBPROD1> set linesize 300
USER1@PDBPROD1> set pagesize 300
USER1@PDBPROD1> col companyname for a30
USER1@PDBPROD1> col phone for 99999999999
USER1@PDBPROD1> col email for a30
USER1@PDBPROD1> select * from people;
ID NAME ID_CARD_NUMBER SEX BIRTHDAY COMPANYNAME PHONE EMAIL
---------- -------------------- ------------------ ---------- ---------- ------------------------------ ------------ ------------------------------
1 程向欢 998903297003105622 男 1989-08-20 吉林本健生物技术股份有限公司 17562635371 yuanmingze787@gmail.com
2 傅永 263329186803313901 女 1984-02-23 忻州易京文化传媒有限公司 13522672709 kewei239@163.com
3 黄宁 560555215710301609 女 1970-10-04 绵阳春升教育咨询有限公司 17864884535 pangmei120@qq.com
7 夏晴娴 792459186810104789 男 1981-05-28 鞍山四海友诚装饰品股份有限公司 14965784873 xueyugang344@163.com
8 苏小 894179182705302265 女 2020-05-17 白银茂长生物技术集团有限公司 13340063183 dongwen117@qq.com
9 郝希齐 358928183312203948 男 1970-05-24 广元娇建培训集团有限公司 15438982015 daiyuanlan382@qq.com
10 曲新 414835394001209712 女 1989-04-04 咸阳美威科技集团有限公司 14993593698 tongrong880@gmail.com
11 辛一诺 927866289504313248 女 2018-06-11 衢州晨鑫工程技术股份有限公司 18562767880 peihua303@163.com
12 白雨桐 61145121091031771X 女 1981-08-14 伊春健圆文化集团有限公司 19889214484 huangmanting136@qq.com
13 谷浩然 729462231101302467 女 1982-11-27 佳木斯凤尚医药科技股份有限公司 15749326665 guoxinyi567@163.com
15 龚欣怡 518439192212204497 男 1992-08-30 三亚诺拓物流运输股份有限公司 19809420459 zhuangxiyuan919@qq.com
16 孔宇航 231819194212042424 男 2002-09-17 乐山玉思电力设备集团有限公司 18372778598 xiaoxiangguang761@gmail.com
17 辛金 439897363807310147 女 1993-04-03 恩施土家族苗族德美商贸有限公司 14944062965 zhaohaoran515@gmail.com
18 乔美彩 160707220406209525 女 1976-05-29 平顶山京德园林绿化工程有限公司 13658208990 maoxincan615@gmail.com
19 卢俊 786514320010207528 女 2022-11-06 识娇婚庆服务股份有限公司 17526097533 oulin042@qq.com
25 屈勤诚 748381189111313308 女 2001-06-14 十堰尔飞机械设备集团有限公司 17569406333 kongxinyao478@gmail.com
16 rows selected.
USER1@PDBPROD1> 解题思路#
方法一：生成序列 + MINUS（最直观）#
思路：先生成 1~25 的完整序列，再用 MINUS 减掉表里存在的 id，剩下的就是缺失值。
SELECT LEVEL AS missing_id
FROM dual CONNECT BY LEVEL <=
(SELECT max(id)
FROM people)
MINUS
SELECT id
FROM people;执行如下：
USER1@PDBPROD1> SELECT LEVEL AS missing_id
FROM dual CONNECT BY LEVEL <=
(SELECT max(id)
FROM people)
MINUS
SELECT id
FROM people; 2 3 4 5 6 7
MISSING_ID
----------
4
5
6
14
20
21
22
23
24
9 rows selected.
USER1@PDBPROD1> 方法二：生成序列 + LEFT JOIN 反连接（更“标准 SQL”）#
思路：同样先生成 1~25 序列，然后 LEFT JOIN 原表，筛 p.id is null。
SELECT seq.id AS missing_id
FROM
(SELECT LEVEL AS id
FROM dual CONNECT BY LEVEL <=
(SELECT max(id)
FROM people)) seq
LEFT JOIN people p ON p.id = seq.id
WHERE p.id IS NULL
ORDER BY seq.id;执行如下：
USER1@PDBPROD1> SELECT seq.id AS missing_id
FROM
(SELECT LEVEL AS id
FROM dual CONNECT BY LEVEL <=
(SELECT max(id)
FROM people)) seq
LEFT JOIN people p ON p.id = seq.id
WHERE p.id IS NULL
ORDER BY seq.id; 2 3 4 5 6 7 8 9
MISSING_ID
----------
4
5
6
14
20
21
22
23
24
9 rows selected.
USER1@PDBPROD1> 方法三：LEAD 找缺口区间 + 展开（大范围更推荐）#
思路：使用LEAD()窗口函数找到每个 id 的下一个值 next_id，next_id > id + 1 说明中间有缺失，查询出缺失范围，最后用CROSS APPLY 展开。
WITH gap_ranges AS
(SELECT id + 1 AS gap_start,
next_id - 1 AS gap_end
FROM
(SELECT id,
lead(id) OVER (ORDER BY id) AS next_id
FROM people)
WHERE next_id > id + 1)
SELECT x.missing_id
FROM gap_ranges g CROSS apply
(SELECT g.gap_start + LEVEL - 1 AS missing_id
FROM dual CONNECT BY LEVEL <= g.gap_end - g.gap_start + 1) x
ORDER BY x.missing_id;执行如下：
USER1@PDBPROD1> WITH gap_ranges AS
(SELECT id + 1 AS gap_start,
next_id - 1 AS gap_end
FROM
(SELECT id,
lead(id) OVER (ORDER BY id) AS next_id
FROM people)
WHERE next_id > id + 1)
SELECT x.missing_id
FROM gap_ranges g CROSS apply
2 3 4 5 6 7 8 9 (SELECT g.gap_start + LEVEL - 1 AS missing_id
FROM dual CONNECT BY LEVEL <= g.gap_end - g.gap_start + 1) x
ORDER BY x.missing_id; 10 11 12 13
MISSING_ID
----------
4
5
6
14
20
21
22
23
24
9 rows selected.
USER1@PDBPROD1> 感谢刘老虎老师的提醒，要确认边界否则头和尾id被删除，就会找不到缺失的id
适用于Oracle版本12c以上的，修改如下:
WITH gap_ranges AS
(SELECT id + 1 AS gap_start,
next_id - 1 AS gap_end
FROM
(SELECT id,
lead(id) OVER (ORDER BY id) AS next_id
FROM
(SELECT id
FROM people
UNION ALL SELECT 0
FROM dual
UNION ALL SELECT 26
FROM dual))
WHERE next_id > id + 1)
SELECT x.missing_id
FROM gap_ranges g CROSS apply
(SELECT g.gap_start + LEVEL - 1 AS missing_id
FROM dual CONNECT BY LEVEL <= g.gap_end - g.gap_start + 1) x
ORDER BY x.missing_id;适用于Oracle版本12c以下的，修改如下:
WITH gap_ranges AS
(SELECT id + 1 AS gap_start,
next_id - 1 AS gap_end
FROM
(SELECT id,
lead(id) OVER (ORDER BY id) AS next_id
FROM
(SELECT id
FROM people
UNION ALL SELECT 0
FROM dual
UNION ALL SELECT 26
FROM dual))
WHERE next_id > id + 1)
SELECT gap_start + LEVEL - 1 AS missing_id
FROM gap_ranges CONNECT BY LEVEL <= gap_end - gap_start + 1
AND
PRIOR gap_start = gap_start
AND
PRIOR sys_guid() IS NOT NULL
ORDER BY missing_id;还有用row_number()（窗口函数） 来代替 LEAD()，再用 自连接 rn+1 找到“下一行 id”，最后把缺口区间展开成单个缺失值。
WITH s AS
(SELECT DISTINCT id,
row_number() OVER (ORDER BY id) AS rn
FROM
(SELECT id
FROM people
UNION ALL SELECT 0
FROM dual
UNION ALL SELECT 26
FROM dual)),
gaps AS
(SELECT a.id + 1 AS gap_start,
b.id - 1 AS gap_end
FROM s a
JOIN s b ON b.rn = a.rn + 1
WHERE b.id > a.id + 1)
SELECT gap_start + LEVEL - 1 AS missing_id
FROM gaps CONNECT BY LEVEL <= gap_end - gap_start + 1
AND
PRIOR gap_start = gap_start
AND
PRIOR sys_guid() IS NOT NULL
ORDER BY missing_id;方法四：MODEL 子句（进阶写法，可读性一般但很“Oracle”）#
思路：把 1~25 当成“维度”，用 MODEL 把缺失位置的 flag 补成 0，然后筛出 flag=0 的维度值。
SELECT id AS missing_id
FROM
(SELECT id,
flag
FROM
(SELECT id,
1 AS flag
FROM people) model dimension by(id) measures(flag) rules upsert(flag [ for id from 1 to 25 increment 1 ] = nvl(flag [ cv() ], 0)))
WHERE flag = 0;执行如下：
USER1@PDBPROD1> SELECT id AS missing_id
FROM
(SELECT id,
flag
FROM
(SELECT id,
1 AS flag
FROM people) model dimension by(id) measures(flag) rules upsert(flag [ for id from 1 to 25 increment 1 ] = nvl(flag [ cv() ], 0)))
WHERE flag = 2 3 4 5 6 7 8 9 0;
MISSING_ID
----------
4
5
6
14
20
21
22
23
24
9 rows selected.
USER1@PDBPROD1> 总结#
本文给了 4 种“一条 SQL”的写法，核心都是：定义范围 → 找出缺失 → 输出缺失编号。如果还有其他方法也请各位高手赐教。