嵌套查询常被误用于序列号断点检测，因其无法天然表达“上一行”关系，强行用相关子查询（如select max(id) from t where id

为什么嵌套查询常被误用于序列号断点检测

直接用嵌套查询找不连续序列号，多数时候是绕远路。核心问题在于：SQL 本身没有“上一行”概念，SELECT 子句里不能引用同级查询的前一条记录；强行用 (SELECT MAX(id) FROM t WHERE id 这类相关子查询，会导致对每行都全表扫描一次，数据量过千就明显变慢。

用窗口函数替代嵌套查询才是正解

现代 SQL（PostgreSQL、SQL Server、MySQL 8.0+、Oracle）都支持 LAG() 和 ROW_NUMBER()，它们在单次扫描中就能算出相邻关系，性能差一个数量级。

• LAG(id) OVER (ORDER BY id) 返回上一行的 id，和当前 id 相减即可判断是否跳号
• 若原始数据有重复或空缺需严格按自然序号比对，用 ROW_NUMBER() OVER (ORDER BY id) 生成期望值，再与实际 id 比较
• 注意 ORDER BY 必须明确且有索引支撑，否则 LAG() 结果不可靠

SELECT id, prev_id
FROM (
  SELECT id, LAG(id) OVER (ORDER BY id) AS prev_id
  FROM serials
) t
WHERE id - prev_id > 1;

MySQL 5.7 或 SQLite 等旧环境怎么办

这些系统不支持窗口函数，但也不该退回去写相关子查询。更可行的是用自连接或变量模拟，不过要注意执行计划和初始化顺序。

• 自连接方式：JOIN serials s1 ON s1.id = s2.id + 1 找出所有“有前驱”的记录，再用 NOT EXISTS 取补集——这比相关子查询快，且能走索引
• MySQL 5.7 可用用户变量：@prev := id 配合 ORDER BY，但必须确保无并发写入且语句不被优化器重排
• SQLite 推荐用 ROWID 辅助（如果表无显式主键），但仅限于物理连续场景，逻辑序列仍需额外处理

边界情况比语法更值得警惕

实际业务中，序列号可能含前导零、字母后缀、时间戳混合等非纯数字格式。此时 LAG() 或自连接会失效，必须先清洗或转为可比较的数值/时间类型。

• 字符串型序列如 'SN-00123'，得用 CAST(SUBSTR(id, 4) AS SIGNED) 提取数字部分再计算
• 存在删除又重建的 ID，比如 1,2,4,5,7，断点是 3 和 6，但若业务要求“最大连续段起点”，就得用分组识别岛屿（island）而非简单差值
• 千万级数据下，即使用了 LAG()，没给 id 加索引，排序阶段仍会磁盘临时表溢出

真正卡住人的从来不是怎么写嵌套查询，而是没想清楚：这个“序列号”到底由谁保证唯一性？是否允许跳号？断点之后的修复动作是什么？这些决定了该查间隙，还是查缺失值，还是查异常插入。