如何在SQL Server 2016中实现自定义行号生成？

原始数据 #

包含PN、CN、Row Number列的源数据如下：

[PN] [CN] [Row Number]
NULL 1    NULL
NULL 2    NULL
NULL 3    NULL
NULL 11   NULL
NULL 12   NULL
1    4    1
1    5    2
2    6    1
3    7    1
3    8    2
3    9    3
3    10   4
4    13   1

期望输出 #

需生成New Row Number列，最终结果如下：

[PN] [CN] [Row Number] [New Row Number]
NULL 1    NULL         1
NULL 2    NULL         2
NULL 3    NULL         3
NULL 11   NULL         4
NULL 12   NULL         5
1    4    1            6
1    5    2            6
2    6    1            7
3    7    1            8
3    8    2            8
3    9    3            8
3    10   4            8
4    13   1            9

核心需求逻辑 #

• 所有PN为NULL的行，每行对应独立的递增行号；
• PN非NULL的分组中，仅当Row Number = 1时触发行号递增，同一PN下的其他行继承该递增后的行号；
• 数据排序依据为PN、CN、Row Number，CN是每行唯一标识，且PN为NULL时Row Number必然为NULL。

SQL Server 2016实现方案 #

通过窗口函数SUM() OVER()实现累计计数，代码如下：

SELECT 
    PN,
    CN,
    [Row Number],
    -- 标记需要触发行号递增的行，累计求和得到新行号
    SUM(CASE 
        WHEN PN IS NULL THEN 1
        WHEN [Row Number] = 1 THEN 1
        ELSE 0
    END) OVER (
        ORDER BY PN, CN, [Row Number] 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS [New Row Number]
FROM YourTableName
ORDER BY PN, CN, [Row Number];

代码说明 #

1. CASE语句标记需要递增的行：PN为NULL或Row Number = 1时返回1，否则返回0；
2. SUM() OVER()窗口函数按指定排序规则，累计从起始行到当前行的标记值，得到的累计值即为New Row Number；
3. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW确保累计范围是当前行及之前的所有行，显式声明使逻辑更清晰。

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