SQL Server 2014无循环更新两组条件间行的技术问询

你已经摸到了正确的方向——用窗口函数和CTE完全可以替代循环来实现这个批量更新需求，不用再依赖FETCH那种逐行处理的方式了。下面是针对你场景的完整解决方案：

完整更新脚本 #

WITH cte_ranked AS (
    -- 为每个ID的行按日期排序编号，获取前后行的类别与日期信息
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY IDStartDate, IDEndDate) AS RowNum,
        LAG(Category) OVER(PARTITION BY ID ORDER BY IDStartDate, IDEndDate) AS prev_category,
        LAG(IDEndDate) OVER(PARTITION BY ID ORDER BY IDStartDate, IDEndDate) AS prev_end_date,
        LEAD(Category) OVER(PARTITION BY ID ORDER BY IDStartDate, IDEndDate) AS next_category
    FROM test
),
cte_intervals AS (
    -- 标记需要更新的区间起始行，计算区间的StartDate
    SELECT 
        *,
        CASE 
            WHEN Category IS NULL AND prev_category = 'S' AND next_category = 'M' 
            THEN DATEADD(DAY, 1, prev_end_date) 
            ELSE NULL 
        END AS interval_start,
        -- 用行号作为区间唯一标识
        CASE 
            WHEN Category IS NULL AND prev_category = 'S' AND next_category = 'M' 
            THEN RowNum 
            ELSE NULL 
        END AS interval_id
    FROM cte_ranked
),
cte_interval_end AS (
    -- 为每个区间找到对应的结束日期（最后一行'M'的IDEndDate）
    SELECT 
        i1.ID,
        i1.interval_id,
        i1.interval_start,
        MAX(i2.IDEndDate) AS interval_end
    FROM cte_intervals i1
    LEFT JOIN cte_intervals i2 
        ON i1.ID = i2.ID 
        AND i2.RowNum > i1.interval_id 
        AND i2.Category = 'M'
        -- 确保只取连续的'M'行，中间不能出现非'M'/非空的行
        AND NOT EXISTS (
            SELECT 1 
            FROM cte_intervals i3 
            WHERE i3.ID = i2.ID 
            AND i3.RowNum BETWEEN i1.interval_id AND i2.RowNum 
            AND i3.Category NOT IN ('M', NULL)
        )
    WHERE i1.interval_id IS NOT NULL
    GROUP BY i1.ID, i1.interval_id, i1.interval_start
)
-- 批量更新区间内的所有行
UPDATE t
SET 
    StartDate = ce.interval_start,
    EndDate = ce.interval_end
FROM test t
JOIN cte_ranked r 
    ON t.ID = r.ID 
    AND t.IDStartDate = r.IDStartDate 
    AND t.IDEndDate = r.IDEndDate
JOIN cte_interval_end ce 
    ON r.ID = ce.ID 
    AND r.RowNum BETWEEN ce.interval_id AND (
        -- 找到区间的结束行号：下一个非'M'/非空行的前一行，或者该ID的最后一行
        SELECT MIN(RowNum) - 1 
        FROM cte_ranked r2 
        WHERE r2.ID = ce.ID 
        AND r2.RowNum > ce.interval_id 
        AND r2.Category NOT IN ('M', NULL)
        UNION ALL SELECT MAX(RowNum) FROM cte_ranked r3 WHERE r3.ID = ce.ID
    );

代码逻辑说明 #

1. cte_ranked：给每个ID分组的行按日期排序并生成行号，同时用LAG/LEAD窗口函数获取前后行的类别、日期，这是后续识别区间的基础。
2. cte_intervals：筛选出符合条件的区间起始行——也就是Category为空、前一行是'S'且后一行是'M'的行，同时计算出该区间的StartDate（前一行'S'的IDEndDate加1天），并用行号作为区间的唯一标识。
3. cte_interval_end：通过左连接找到每个起始行之后的所有连续'M'行，取这些行中最大的IDEndDate作为区间的EndDate，NOT EXISTS条件确保中间不会混入非'M'、非空的行，避免跨区间错误。
4. 更新操作：将原始表与CTE关联，定位到每个区间内的所有行（从起始行到最后一行'M'），批量更新StartDate和EndDate。

验证结果 #

执行更新后，运行以下查询即可查看目标结果：

SELECT * FROM test ORDER BY ID, IDStartDate;

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