子查询本身无法识别断档，关键在于利用NOT EXISTS来查找ID断层的起点（例如WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id - 1)），同时结合MIN(id)来过滤首行，这要求id必须有索引且非空唯一；而EXCEPT则适用于对已知范围的全量缺值进行枚举。

子查询本身不能直接“识别断档”，它只是工具；真正起作用的是用子查询构造对比逻辑——比如查“前一个ID是否存在”或“下一个日期是否跳变”。关键不在嵌套多深，而在对比关系是否准确表达业务断档定义。

用NOT EXISTS找ID断层起点

这是最轻量、兼容性最好的方式，不依赖窗口函数，MySQL 5.7、PostgreSQL、SQL Server 全支持。核心是把“缺失”转化为“前驱不存在”：

• 写法：WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id - 1)，再加t1.id > (SELECT MIN(id) FROM t1)排除首行误判
• 必须给id建索引，否则子查询会全表扫描，百万行时可能秒变分钟级
• 如果表里有NULL或重复id，结果不可靠——先WHERE id IS NOT NULL AND id > 0过滤
• 它只返回断层起点（如id=5说明4缺失），不直接给出区间；要得gap_start/gap_end，得配合LEAD()或再关联查最大连续后继

用EXCEPT生成全量序列再比对

当你明确知道ID范围（比如订单号从1000到2000），且需要列出所有具体缺值时，EXCEPT比子查询更清晰、执行计划更优：

• 写法：SELECT g.id FROM generate_series((SELECT MIN(id) FROM t), (SELECT MAX(id) FROM t)) AS g(id) EXCEPT SELECT id FROM t（PostgreSQL）
• MySQL 8.0+ 可用WITH RECURSIVE替代generate_series；SQL Server 要加OPTION (MAXRECURSION 0)
• 若表为空，MIN(id)和MAX(id)返回NULL，整个generate_series不执行——这是正确行为，不是bug
• 别无脑写generate_series(1, 1000000)：跨度越大，内存和时间消耗非线性增长；先算出实际MIN/MAX再生成

子查询嵌套LAG()模拟的陷阱

有些人在不支持窗口函数的老版本（如MySQL 5.7）里，用自连接+子查询硬模拟LAG()，但极易翻车：

• 典型错误：SELECT a.id, (SELECT id FROM t b WHERE b.id < a.id ORDER BY b.id DESC LIMIT 1) AS prev_id FROM t a——没加索引时，每行都触发一次全表扫描，O(n)复杂度
• 更糟的是，如果id不唯一，子查询可能返回任意一个前驱值，导致差值计算完全失真
• 安全替代：改用变量模拟（@prev := @prev），但必须保证ORDER BY id在外部查询中生效，否则变量赋值顺序错乱
• 真正该优先检查的，不是怎么模拟LAG()，而是业务是否允许ID重复或跳变——比如支付流水号本就可能因重试重复，这时“断档”根本不是问题

最容易被忽略的不是语法，而是断档定义本身：是按ID自然序断？还是按业务时间戳断？同一张表里，order_id连续但created_at乱序，往往意味着写入链路异常，比ID缺几个号更值得警觉。

《夸克》非常好用的免费AI浏览器

下载APP查看