SQL Server如何为连续相同状态分组生成递增ID且保留全部记录

你遇到的是SQL中经典的连续序列分组（岛屿问题），直接按状态排序分组会把非连续的同状态值分到同一组，可以用状态变化累加的方案实现需求，生成连续递增的分组ID，且保留所有原始记录：

实现SQL #

WITH Step1 AS (
    SELECT 
        *,
        -- 取当前记录上一行的Current_Status，第一行默认返回空字符串
        LAG(Current_Status, 1, '') OVER (PARTITION BY Id ORDER BY Date) AS prev_status
    FROM communicats
),
Step2 AS (
    SELECT 
        *,
        -- 状态和上一行不同时计数+1，相同时计数不变，最终得到分组ID
        SUM(CASE WHEN Current_Status = prev_status THEN 0 ELSE 1 END) 
            OVER (PARTITION BY Id ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Row_ID
    FROM Step1
)
SELECT 
    Id,
    Current_Status,
    Date,
    Other_Columns,
    Row_ID
FROM Step2
ORDER BY Date

逻辑说明 #

1. 先用LAG窗口函数按日期排序，取每条记录的上一行状态值，第一行没有上一行就返回空字符串
2. 逐行判断当前状态和上一行状态是否一致：不一致就累加1，一致就累加0，累加的结果就是连续相同状态组的唯一ID，自动按状态变化顺序递增
3. 如果你的表中只有一个固定Id，可以去掉所有PARTITION BY Id的语句，执行结果不受影响

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