如何在SQL Server中按年月排序并在F2变更时重置序号

测试数据 #

Declare @tbl Table
(
    F1    int,
    F2    int,
    Year  int,
    Month tinyint
)

Insert into @tbl 
values
(10, 1, 2020, 1),
(10, 1, 2020, 2),
(10, 1, 2020, 3),
(10, 1, 2020, 4),
(10, 2, 2020, 5),
(10, 1, 2020, 6),
(10, 1, 2020, 7),
(11, 1, 2020, 1),
(11, 1, 2020, 2),
(11, 2, 2020, 3),
(11, 2, 2020, 4),
(11, 1, 2020, 5),
(12, 1, 2020, 1),
(12, 1, 2020, 2),
(12, 1, 2020, 3),
(12, 1, 2020, 4)

问题分析 #

原查询通过PARTITION BY F1, F2生成序号，会把同一F1下所有相同F2的记录归为同一组，无法区分F2值切换后的新序列（比如F1=10中，F2从1变为2再变回1时，原查询会把前后两个F2=1的序列合并计数）。需要实现的是：在F1分组内按年月排序，每当F2值与上一条记录不同时，序号重置为1并重新计数。

解决方案 #

使用LAG()函数获取当前记录的上一条F2值，判断是否发生变化以生成分组标识，再基于该标识和F1用ROW_NUMBER()生成目标序号：

WITH ranked_data AS (
    SELECT 
        F1, F2, Year, Month,
        -- 生成分组ID：F2与上一条不同时，分组ID递增
        SUM(CASE WHEN LAG(F2) OVER (PARTITION BY F1 ORDER BY Year, Month) = F2 THEN 0 ELSE 1 END) 
            OVER (PARTITION BY F1 ORDER BY Year, Month) AS group_id
    FROM @tbl
)
SELECT 
    F1, F2, Year, Month,
    ROW_NUMBER() OVER (PARTITION BY F1, group_id ORDER BY Year, Month) AS Sequence
FROM ranked_data
ORDER BY F1, Year, Month;

结果验证 #

执行上述查询后，会得到符合预期的结果：

逻辑说明 #

1. LAG(F2) OVER (PARTITION BY F1 ORDER BY Year, Month)：获取同一F1分组内，当前记录的上一条记录的F2值。
2. CASE WHEN ... THEN 0 ELSE 1 END：若当前F2与上一条相同标记为0，不同则标记为1。
3. SUM(...) OVER (PARTITION BY F1 ORDER BY Year, Month)：累计求和生成group_id，每次F2变化时group_id递增，以此区分不同的连续F2序列。
4. ROW_NUMBER()基于F1和group_id分组、按年月排序生成序号，实现F2变化时重置计数。

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