SQL Server中OVER/PARTITION BY场景下STATUS列实现方法问询

要标记同分组下的首尾记录，直接基于你已有的窗口函数逻辑扩展即可：

• 你已经用正序ROW_NUMBER()算出了按时间排序的出现次序SCORE，值为1的就是分组内首次出现的记录
• 只需要补充末次记录的判断逻辑即可：两种实现方式都可以，要么对同分区按时间倒序打行号，倒序行号为1的就是末次；要么直接统计分组总记录数，SCORE等于总记录数的就是末次。

写法1：无嵌套直接改写原SQL #

在你原有SQL的基础上补充STATUS判断逻辑即可，不需要调整原有查询结构：

SELECT 
    RequestNumber,
    Task,
    StartDate,
    ROW_NUMBER() OVER(PARTITION BY RequestNumber, TaskName ORDER BY START_DATE) AS SCORE,
    CASE
        WHEN ROW_NUMBER() OVER(PARTITION BY RequestNumber, TaskName ORDER BY START_DATE) = 1 
         AND ROW_NUMBER() OVER(PARTITION BY RequestNumber, TaskName ORDER BY START_DATE DESC) = 1
            THEN '首次/末次出现'
        WHEN ROW_NUMBER() OVER(PARTITION BY RequestNumber, TaskName ORDER BY START_DATE) = 1
            THEN '首次出现'
        WHEN ROW_NUMBER() OVER(PARTITION BY RequestNumber, TaskName ORDER BY START_DATE DESC) = 1
            THEN '末次出现'
        ELSE NULL
    END AS STATUS
FROM [SOURCE_TABLE]
ORDER BY RequestNumber, START_DATE

写法2：CTE复用计算结果（推荐） #

如果不想重复书写多次窗口函数，用CTE提前算好SCORE和分组总记录数，代码更简洁，执行效率也更高：

WITH task_rn AS (
    SELECT
        RequestNumber,
        Task,
        StartDate,
        ROW_NUMBER() OVER(PARTITION BY RequestNumber, TaskName ORDER BY START_DATE) AS SCORE,
        COUNT(1) OVER(PARTITION BY RequestNumber, TaskName) AS group_total
    FROM [SOURCE_TABLE]
)
SELECT
    RequestNumber,
    Task,
    StartDate,
    SCORE,
    CASE
        WHEN SCORE = 1 AND group_total = 1 THEN '首次/末次出现'
        WHEN SCORE = 1 THEN '首次出现'
        WHEN SCORE = group_total THEN '末次出现'
        ELSE NULL
    END AS STATUS
FROM task_rn
ORDER BY RequestNumber, START_DATE

以你举例的NC2请求下task1有3条记录的场景为例：

• 第1条记录SCORE=1，匹配「首次出现」标识
• 第2条记录SCORE=2，既不是1也不等于总条数3，STATUS返回空值不做标记
• 第3条记录SCORE=3等于分组总条数，匹配「末次出现」标识
  完全符合需求。如果分组内只有1条记录，会标记为「首次/末次出现」，你可以根据业务需要调整对应的标识文本。

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