如何按ID_PO分组重置Num_Linea列的序列编号？

需要实现的需求：给#temp表的Num_Linea列分配序列编号，每个ID_PO分组内从1开始递增，分组切换时重置编号。现有游标代码仅能生成全局连续编号，无法在ID_PO变化时重置计数。

现有游标代码 #

DECLARE @Num_Line as int;
DECLARE @CurNum_Line as int;

SELECT @Num_Line = 1;

DECLARE LINECURSOR CURSOR FOR
    SELECT Num_Linea
    FROM   #temp

OPEN LINECURSOR;

FETCH NEXT FROM LINECURSOR INTO @CurNum_Line

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE #temp
    SET Num_Linea = @Num_Line
    WHERE CURRENT OF LINECURSOR;

    SELECT @Num_Line = @Num_Line + 1;

    FETCH NEXT FROM LINECURSOR INTO @CurNum_Line
END;

CLOSE LINECURSOR;
DEALLOCATE LINECURSOR;

当前结果 #

期望结果 #

解决方案 #

方法1：使用ROW_NUMBER()窗口函数（推荐） #

这是SQL标准的高效实现方式，无需游标，性能远优于游标操作：

UPDATE t
SET Num_Linea = rn
FROM (
    SELECT 
        ID_PO, 
        Num_Linea,
        ROW_NUMBER() OVER (PARTITION BY ID_PO ORDER BY (SELECT NULL)) AS rn
    FROM #temp
) t

说明：PARTITION BY ID_PO 按ID_PO分组，ORDER BY (SELECT NULL) 表示按现有行顺序编号，若需要特定排序逻辑，可替换为实际列（比如ORDER BY 某排序列）。

方法2：修复游标代码 #

若必须使用游标，需添加变量跟踪当前ID_PO，当ID_PO变化时重置编号：

DECLARE @Num_Line as int;
DECLARE @CurID_PO int;
DECLARE @PrevID_PO int;

SELECT @Num_Line = 1, @PrevID_PO = NULL;

-- 游标需同时获取ID_PO和Num_Linea，且按ID_PO排序保证同组连续处理
DECLARE LINECURSOR CURSOR FOR
    SELECT ID_PO, Num_Linea
    FROM   #temp
    ORDER BY ID_PO;

OPEN LINECURSOR;

FETCH NEXT FROM LINECURSOR INTO @CurID_PO, @Num_Line

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 当ID_PO变化时重置编号
    IF @CurID_PO <> @PrevID_PO
    BEGIN
        SET @Num_Line = 1;
    END

    UPDATE #temp
    SET Num_Linea = @Num_Line
    WHERE CURRENT OF LINECURSOR;

    SET @Num_Line = @Num_Line + 1;
    SET @PrevID_PO = @CurID_PO;

    FETCH NEXT FROM LINECURSOR INTO @CurID_PO, @Num_Line
END;

CLOSE LINECURSOR;
DEALLOCATE LINECURSOR;

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