使用组内的增量数字更新表行

Question

我需要更新一个表，以便同一"组"(示例中的代码 + 颜色(的行在组中获得增量数字。每个组的行应编号为 1、2、3...

id   | Code | Color   |Required_New_field
--------------------------
1231 |001   |Red      | 1
1232 |001   |Red      | 2
1233 |002   |Red      | 1
1234 |001   |Red      | 3
1235 |001   |Blue     | 1
1236 |002   |Red      | 2
1237 |001   |Blue     | 2
1238 |002   |Blue     | 1
1239 |002   |Red      | 3
...

在 Code=001 和 Color=Red 的示例行中，应分别获得 1、2、3。

我已经尝试了几种使用子查询和"分组依据"的方法，但我意识到实际上并不是正确的方法。

任何提示都会被理解！

编辑：ROW_NUMBER(( 答案很棒！可悲的是，我必须在旧的sql_server 2000版本上运行它。ROW_NUMBER(( 在 2005 年及以上版本上可用(有关可用性的详细信息，请单击此处(。还有其他选择吗？

Answer 1

您可以使用

row_number()计算数字：

select t.*,
       row_number() over (partition by code, color order by id) as required_new_field
from t;

对于更新，请使用可更新的 CTE：

with toupdate as (
       select t.*,
              row_number() over (partition by code, color order by id) as seqnum
       from t
      )
update toupdate
    set required_new_field = seqnum;

Answer 2

虽然我确实同意每个人的评论，即 SQL Server 2005 中ROW_NUMBER()可用，但这里是使用子查询来COUNT()的替代解决方案。可以在 SQL Server 2000 上使用。请注意，在性能方面，它的成本要高得多：

SELECT 
    t2.*,
    (SELECT COUNT(*) 
     FROM your_table t1 
     WHERE t1.code = t2.code 
       AND t1.color = t2.color
       AND t1.id <= t2.id) AS Rn
FROM 
    your_table t2

编辑 - 更新：

UPDATE t2 
SET RN = (SELECT COUNT(*) 
          FROM your_table t1 
          WHERE t1.code = t2.code 
            AND t1.color = t2.color
            AND t1.id <= t2.id) 
FROM your_table t2