SQL查询表中缺失ID并合并为连续区间两列输出的实现方法

解决方案 #

你可以在原有递归CTE的基础上增加分组逻辑，通过计算missnum和对应行号的差值，将连续的ID划到同一分组，最终分组聚合即可得到连续缺失区间：

;WITH Missing (missnum, maxid)
AS
(
 SELECT 1 AS missnum, (select max(id) from MYTABLE)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
),
-- 筛选出所有缺失的ID
AllMissing AS (
    SELECT missnum
    FROM Missing
    LEFT OUTER JOIN MYTABLE on MYTABLE.id = Missing.missnum
    WHERE MYTABLE.id is NULL
),
-- 给缺失ID分配行号，计算分组标识：连续ID的missnum - 行号值相同
MissingGroups AS (
    SELECT 
        missnum,
        missnum - ROW_NUMBER() OVER (ORDER BY missnum) AS group_id
    FROM AllMissing
)
-- 分组聚合得到连续区间
SELECT 
    MIN(missnum) AS [from],
    MAX(missnum) AS [to]
FROM MissingGroups
GROUP BY group_id
ORDER BY [from]
OPTION (MAXRECURSION 0);

逻辑说明 #

• 第一层CTEMissing和你原有逻辑一致，生成从1到表最大ID的完整连续序列
• 第二层CTEAllMissing筛选出所有未在业务表中出现的缺失ID
• 第三层CTEMissingGroups通过ROW_NUMBER窗口函数生成行号，连续的缺失ID的missnum - 行号值会完全相同，以此作为分组标识
• 最后按分组标识聚合，取每组最小ID为区间起始、最大ID为区间结束，即可得到你需要的连续缺失区间结果，数千行数据量级下该方案性能无问题

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