SQLite表查找缺失记录与序列：如何指定序列起始值为1

要支持预定义序列起始值的缺失检测，你可以通过添加基准校验逻辑，把「预定义起始值到表内首个序列值」的区间也纳入缺口统计范围，修改后的SQL如下：

WITH params AS (
  SELECT 1 AS min_expected_id -- 这里修改为你需要的预定义序列起始值
)
SELECT dir, start, stop 
FROM (
  -- 原有逻辑：检测表内序列之间的缺口
  SELECT m.id + 1 start,
    (SELECT MIN(id) - 1 FROM sequence x WHERE x.__dirpath = m.__dirpath AND x.id > m.id) stop, 
     m.__dirpath dir
  FROM sequence m LEFT JOIN sequence r 
  ON m.__dirpath = r.__dirpath AND m.id = r.id - 1
  WHERE r.id IS NULL

  UNION ALL

  -- 新增逻辑：检测预定义起始值到首个序列值之间的缺口
  SELECT 
    p.min_expected_id AS start,
    MIN(s.id) - 1 AS stop,
    s.__dirpath AS dir
  FROM sequence s, params p
  GROUP BY s.__dirpath, p.min_expected_id
  HAVING MIN(s.id) > p.min_expected_id
) 
WHERE stop IS NOT NULL 
ORDER BY dir, start, stop;

逻辑说明 #

• 新增的params公共表表达式用来统一管理预定义起始值，需要调整起始值时只需修改这里的min_expected_id即可，无需改动后续查询逻辑。
• 新增的UNION ALL分支会对每个路径（__dirpath）单独校验：如果该路径下存储的最小序列ID大于预定义起始值，就会生成从起始值到首个ID前一位的缺口区间。
• 最终结果会合并原有内部缺口和起始端缺口，完全覆盖需要的检测场景。

比如你设置预定义起始值为1，某路径下首个序列值为5时，查询结果会额外返回start=1, stop=4的记录，刚好补充原有逻辑缺失的检测范围。

内容的提问来源于stack exchange，提问作者evand