如何在SQL中提取同一CL_Name下连续ROW_NUMBER的最大值记录

要实现提取同一CL_Name下每个连续ROW_NUMBER序列的最大值对应记录，核心是用间隙与孤岛算法识别连续序列，再筛选每个序列的最大值。

步骤说明 #

1. 识别连续序列：对每个CL_Name分组，按ROW_NUMBER降序生成组内序号，用原ROW_NUMBER减去这个组内序号，得到的相同值即为同一个连续序列的标识（记为gap_id）。
2. 筛选序列最大值：在每个CL_Name+gap_id的分组中，选取ROW_NUMBER最大的记录。

完整SQL代码 #

WITH ranked_data AS (
    SELECT 
        [_NAME],
        [LINE],
        [TIMESTAMP],
        [Equipment],
        [CL_Name],
        [Status],
        [ROW_NUMBER],
        -- 计算连续序列标识：同一连续序列的gap_id值相同
        [ROW_NUMBER] - ROW_NUMBER() OVER (PARTITION BY [CL_Name] ORDER BY [ROW_NUMBER] DESC) AS gap_id
    FROM [dbo].[Deflection]
    WHERE [TIMESTAMP] > DATEADD(day,-1,GETDATE()) AND [Status] = 0
),
max_row_per_group AS (
    SELECT 
        [_NAME],
        [LINE],
        [TIMESTAMP],
        [Equipment],
        [CL_Name],
        [Status],
        [ROW_NUMBER],
        -- 标记每个序列中的最大ROW_NUMBER记录
        ROW_NUMBER() OVER (PARTITION BY [CL_Name], gap_id ORDER BY [ROW_NUMBER] DESC) AS seq_rank
    FROM ranked_data
)
SELECT 
    [_NAME],
    [LINE],
    [TIMESTAMP],
    [Equipment],
    [CL_Name],
    [Status],
    [ROW_NUMBER]
FROM max_row_per_group
WHERE seq_rank = 1
ORDER BY [ROW_NUMBER] DESC, [TIMESTAMP] DESC;

代码解释 #

• ranked_data CTE：通过ROW_NUMBER() OVER (PARTITION BY [CL_Name] ORDER BY [ROW_NUMBER] DESC)生成每个CL_Name下的组内序号，用原ROW_NUMBER减去这个序号，同一个连续序列的结果值一致，以此区分不同的连续段。
• max_row_per_group CTE：在每个CL_Name+gap_id的分组中，按ROW_NUMBER降序生成序号，序号为1的就是该序列的最大值记录。
• 最后筛选seq_rank = 1的记录，得到期望结果。

验证结果 #

执行上述SQL后，会输出每个CL_Name下各连续ROW_NUMBER序列的最大值记录，与你给出的期望输出完全匹配。

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