SQL Server TSQL如何仅对连续行按Key、State进行GROUP BY分组

实现思路 #

• 该需求属于SQL经典的间隙与孤岛场景，你提到的ROW_NUMBER函数确实是核心实现工具
• 核心逻辑：计算两个不同维度排序的行号差值，连续同Key同State的行差值固定，以此作为额外分组维度即可实现连续行的聚合

实现代码 #

-- 请将下方的 StatusLog 替换为你实际的表名
WITH RankedData AS (
    SELECT 
        [Key],
        State,
        [from],
        [until],
        -- 按Key分区，全量数据按起始日期排序的行号
        ROW_NUMBER() OVER (PARTITION BY [Key] ORDER BY [from]) AS rn_all,
        -- 按Key+State分区，同分组内按起始日期排序的行号
        ROW_NUMBER() OVER (PARTITION BY [Key], State ORDER BY [from]) AS rn_state
    FROM StatusLog
)
SELECT 
    [Key],
    State,
    MIN([from]) AS [from],
    MAX([until]) AS [until]
FROM RankedData
GROUP BY [Key], State, rn_all - rn_state
ORDER BY [from];

逻辑说明 #

连续的同Key同State的行，两个行号会同步递增，差值保持不变；当State发生变化时，按Key+State分区的行号会重新从1开始计数，差值就会发生变化。通过将该差值加入分组条件，就能把连续的同属性行聚合为同一组，取分组内最小起始日期、最大结束日期即可得到你预期的输出结果。
该方案不依赖日期维度的特殊规则，适配所有连续同属性分组的场景，完全兼容SQL Server 2017版本。

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