tsql - 设置顺序值而不循环/游标

Question

我需要在数据表中设置一个非唯一标识符。这在组内是连续的，即。对于每个组，ID 应从 1 开始，并以 1 为增量递增，直到该组的最后一行。

下表对此进行了说明。 “新 ID”是我需要填充的列。

Unique ID  Group ID  New ID
---------  --------  ------
1          1123      1
2          1123      2
3          1124      1
4          1125      1
5          1125      2
6          1125      3
7          1125      4

有没有什么方法可以在不循环/游标的情况下做到这一点？如果循环/游标是唯一的方法，那么最有效的代码是什么？

谢谢

Answer 1

一种方法是在带有子查询的 UPDATE...FROM 语句中使用 ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) /code> 在 FROM 子句 中。

update MyTable set NewID = B.NewID
from
MyTable as A
inner join (select UniqueID, ROW_NUMBER() over (partition by GroupID order by UniqueID) as NewID from MyTable) as B on B.UniqueID = A.UniqueID

• MSDN 有一个很好的示例可以帮助您入门：
• 您需要使用FROM 子句中的子查询，以便利用 Windows 函数 (Row_Index())
• Partition By 告诉服务器何时重置行号
• Order By 告诉服务器以哪种方式对组的 NewID 进行排序

Answer 2

我同意 Damien 在评论中的观点，但您不需要 JOIN 您可以直接更新 CTE。

;WITH cte AS
(
SELECT [New ID],
       ROW_NUMBER() OVER (PARTITION BY [Group ID] ORDER BY [Unique ID]) AS _NewID
FROM @T       
)
UPDATE cte
SET [New ID] = _NewID

在线演示

Answer 3

如果您使用的是 SS 2000，请替换为 RowNumber()

SELECT  UniqueID,
        GroupID,
        (SELECT COUNT(T2.GroupID)
        FROM    myTable T2
        WHERE   GroupID <= T1.GroupID) AS NewID
FROM    myTable T1

Answer 4

如果您运行的是旧版本的 mssql，此解决方案也适用

--Test table:

DECLARE @t table(Unique_ID int, Group_ID int,   New_ID int)

--Test data:

INSERT @t (unique_id, group_id)
SELECT 1, 1123 UNION ALL SELECT 2, 1123 UNION ALL SELECT 3, 1124 UNION ALL SELECT 4, 1125 UNION ALL SELECT 5, 1125 UNION ALL SELECT 6, 1125 UNION ALL SELECT 7, 1125 

--Syntax:

UPDATE t 
SET new_id = 
    (SELECT count(*) 
    FROM @t 
    WHERE t.unique_id >= unique_id and t.group_id = group_id 
    GROUP BY group_id) 
FROM @t t

--Result:

SELECT * FROM @t 

Unique_ID   Group_ID    New_ID
----------- ----------- -----------
1           1123        1
2           1123        2
3           1124        1
4           1125        1
5           1125        2
6           1125        3
7           1125        4

Answer 5

SELECT 
    UniqueId,
    GroupID,
    ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY UniqueId) AS NewIdx
FROM
    ....