查找SQLite表中缺失的记录和缺失的序列-其中序列中的第一个条目必须为1

Question

原始问题和解决方法请参见识别SQLite表中缺失的序列。

下面的代码正确地识别了缺失的序列，但是，根据表中的数据，它的起点是序列中最低的数字。例如，如果序列应该从1开始，但是数据中的第一个序列从5开始，那么1…

关于如何使用预定义的开始序列进行测试的任何提示?

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
) 
WHERE stop IS NOT NULL 
ORDER BY dir, start, stop;

Answer 1

您所要做的就是在数据集中包含每个__dirpath的一行，id等于0，前提是这样的行不存在于表中，因为最小可能的id是1.
您可以在CTE中使用UNION ALL.
然后在您的查询中使用该CTE:

WITH cte AS (
SELECT __dirpath, id FROM sequence
UNION ALL
SELECT DISTINCT __dirpath, '0' FROM sequence 
)
SELECT dir, start, stop 
FROM (
SELECT m.id + 1 start,
(SELECT MIN(id) - 1 FROM cte x WHERE x.__dirpath = m.__dirpath AND x.id > m.id) stop, 
m.__dirpath dir
FROM cte m LEFT JOIN cte r 
ON m.__dirpath = r.__dirpath AND m.id = r.id - 1
WHERE r.id IS NULL
) 
WHERE stop IS NOT NULL 
ORDER BY dir, start, stop;