如何使用递归CTE识别并合并康复中心成员的连续入住日期区间？

需要处理康复中心的成员入住管理表（administered表），该表记录了成员的入住日期（START）和出院日期（END）。需求是识别成员的连续入住日期区间（包括出院次日即再次入住的情况），并将这些连续区间合并为单一的日期区间，仅输出存在连续区间的成员信息。

样例表 #

期望输出 #

示例说明：若一名成员2015年7月5日入住，10月3日出院，10月4日再次入住并于12月7日出院，需将其日期区间合并为2015年7月5日至12月7日。

我的尝试（未成功） #

我尝试编写了如下递归CTE查询，但不清楚如何正确设置基例和终止条件，无法得到正确结果：

SELECT id, mimed, start, end
FROM administered

UNION ALL

(
 SELECT n.id, n.memid, n.start, n.end
     FROM administered n JOIN result r on n.memid = r.memid
     WHERE n.start = DATEADD(day, 1, r.end)

)

)

SELECT MIN(start) as start, MAX(end) as end, memid
FROM result
GROUP BY memid
OPTION(MAXRECURSION 0)

正确解决方案：使用窗口函数实现区间合并 #

递归CTE并非最优选择，用窗口函数可以更简洁高效地完成连续区间识别与合并：

WITH ranked_stays AS (
    SELECT 
        MEMID,
        START,
        END,
        -- 标记当前入住是否与上一入住连续/重叠，生成分组ID
        SUM(CASE WHEN DATEADD(day, 1, LAG(END) OVER (PARTITION BY MEMID ORDER BY START)) >= START THEN 0 ELSE 1 END) 
            OVER (PARTITION BY MEMID ORDER BY START) AS group_id
    FROM administered
),
grouped_stays AS (
    SELECT 
        MEMID,
        MIN(START) AS merged_start,
        MAX(END) AS merged_end,
        COUNT(*) AS stay_count
    FROM ranked_stays
    GROUP BY MEMID, group_id
)
-- 仅输出存在连续区间的成员（即同一成员有多个合并前的入住记录）
SELECT 
    MEMID AS [MEMID（成员ID）],
    merged_start AS [START（入住日期）],
    merged_end AS [END（出院日期）]
FROM grouped_stays
WHERE stay_count > 1
ORDER BY MEMID;

代码逻辑说明 #

1. ranked_stays 阶段：

   • 按成员ID分组，按入住日期排序
   • 用LAG(END)获取当前成员上一次的出院日期，判断当前入住是否与上一次连续（出院次日或更早入住，含重叠）
   • 通过累加标记值生成group_id，同一连续区间的记录会被分配到同一个分组

2. grouped_stays 阶段：

   • 按成员ID和group_id分组，取每组的最早入住日期和最晚出院日期，得到合并后的完整区间
   • 统计每个分组的入住记录数，用于筛选需要合并的成员

3. 最终查询：

   • 只保留stay_count > 1的记录，即该成员存在至少两个连续/重叠的入住区间，符合输出要求

执行结果 #

上述代码针对样例数据运行后，将得到与期望输出完全一致的结果。

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