SQL Server中Row_Number按列分组编号问题求助

我在使用ROW_NUMBER()函数按PR_Cmd分组、基于PR_Expd进行编号时遇到了困难。

我的数据集如下：

PR_Cmd     PR_Expd
--------------------------
CVP909104  LVP1ET03904305
CVP909105  LVP1ET03904306
CVP909105  LVP1ET03904306
CVP909105  LVP1ET03904306
CVP909105  LVP1ET03904306
CVP909105  LVP1ET03904306
CVP909105  LVP1ET03904307
CVP909106  LVP1ET03904308

我期望得到的结果是：

PR_Cmd     PR_Expd          Expd_Number
-------------------------------------------
CVP909104  LVP1ET03904305   1
CVP909105  LVP1ET03904306   1
CVP909105  LVP1ET03904306   1
CVP909105  LVP1ET03904306   2
CVP909105  LVP1ET03904307   3
CVP909106  LVP1ET03904308   1

需要实现上述Expd_Number列的编号逻辑。

从你给出的期望结果来看，编号规则比较特殊，可能存在输入笔误。我先给出几种常见场景的解决方案，你可以根据实际需求调整：

方案1：同一PR_Cmd下，不同PR_Expd分配唯一编号（重复值编号相同） #

如果需求是同一PR_Cmd分组内，每个不同的PR_Expd对应唯一编号（不管重复出现多少次），可以用DENSE_RANK()：

SELECT 
    PR_Cmd,
    PR_Expd,
    DENSE_RANK() OVER (PARTITION BY PR_Cmd ORDER BY PR_Expd) AS Expd_Number
FROM your_table;

执行结果：

PR_Cmd     PR_Expd          Expd_Number
-------------------------------------------
CVP909104  LVP1ET03904305   1
CVP909105  LVP1ET03904306   1
CVP909105  LVP1ET03904306   1
CVP909105  LVP1ET03904306   1
CVP909105  LVP1ET03904306   1
CVP909105  LVP1ET03904307   2
CVP909106  LVP1ET03904308   1

方案2：同一PR_Cmd+PR_Expd组合内，按出现顺序编号 #

如果需要对同一PR_Cmd下的每个PR_Expd实例按出现顺序编号（同一个PR_Expd每出现一次编号递增），可以用ROW_NUMBER()同时按两个字段分组：

SELECT 
    PR_Cmd,
    PR_Expd,
    ROW_NUMBER() OVER (PARTITION BY PR_Cmd, PR_Expd ORDER BY (SELECT NULL)) AS Expd_Number
FROM your_table;

执行结果：

PR_Cmd     PR_Expd          Expd_Number
-------------------------------------------
CVP909104  LVP1ET03904305   1
CVP909105  LVP1ET03904306   1
CVP909105  LVP1ET03904306   2
CVP909105  LVP1ET03904306   3
CVP909105  LVP1ET03904306   4
CVP909105  LVP1ET03904306   5
CVP909105  LVP1ET03904307   1
CVP909106  LVP1ET03904308   1

方案3：同一PR_Cmd下，连续相同PR_Expd为一组，组编号递增（岛屿问题） #

如果需求是把连续相同的PR_Expd视为一个“岛屿”，每个岛屿分配递增编号（即使同一个PR_Expd不连续出现也会重新编号），可以用以下查询：

WITH ranked_data AS (
    SELECT 
        PR_Cmd,
        PR_Expd,
        LAG(PR_Expd) OVER (PARTITION BY PR_Cmd ORDER BY (SELECT NULL)) AS prev_expd
    FROM your_table
),
grouped_data AS (
    SELECT 
        PR_Cmd,
        PR_Expd,
        SUM(CASE WHEN PR_Expd = prev_expd THEN 0 ELSE 1 END) OVER (PARTITION BY PR_Cmd ORDER BY (SELECT NULL)) AS grp
    FROM ranked_data
)
SELECT 
    PR_Cmd,
    PR_Expd,
    grp AS Expd_Number
FROM grouped_data;

比如如果数据中有中断的PR_Expd：

CVP909105  LVP1ET03904306
CVP909105  LVP1ET03904306
CVP909105  LVP1ET03904307
CVP909105  LVP1ET03904306

执行结果会是：

PR_Cmd     PR_Expd          Expd_Number
-------------------------------------------
CVP909105  LVP1ET03904306   1
CVP909105  LVP1ET03904306   1
CVP909105  LVP1ET03904307   2
CVP909105  LVP1ET03904306   3

如果以上方案都不符合你的需求，可以补充说明更详细的编号规则，比如什么时候编号递增、什么时候保持不变。

内容的提问来源于stack exchange，提问作者Mohammed El Mehdi Kherrachi