如何在只读权限下查找自增列seqno中的缺失值？

你的原SQL无法正确返回所有缺失值，原因是ROW_NUMBER()仅对现有行进行连续编号，只能定位到seqno与行号不匹配的位置，无法生成中间所有缺失的连续数值。比如你的示例数据中，原SQL会返回2、5、6，但漏掉了9，且无法把5、6拆分为两个独立的缺失值。

正确解决方案（递归CTE法） #

利用递归CTE生成从seqno最小值到最大值的所有连续数字，再与原表左连接筛选出未匹配的数值：

WITH num_sequence AS (
    SELECT MIN(seqno) AS num
    FROM trnmast
    UNION ALL
    SELECT num + 1
    FROM num_sequence
    WHERE num < (SELECT MAX(seqno) FROM trnmast)
)
SELECT num AS missing_seqno
FROM num_sequence
LEFT JOIN trnmast ON num_sequence.num = trnmast.seqno
WHERE trnmast.seqno IS NULL
OPTION (MAXRECURSION 0); -- 当seqno范围超过100时必须添加此选项

说明 #

• 递归CTEnum_sequence会生成从表中最小seqno到最大seqno的所有连续整数。
• 左连接原表后，trnmast.seqno IS NULL的行即为缺失的数值，对应你的示例会返回2、5、6、9。
• 此方法无需修改表或创建临时对象，仅需只读权限即可执行。

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