TSQL - UPDATE语句中的自动增量

SQL Server 2005

SQL Server 2005

I have a table containing the following: -

我有一张包含以下内容的表： -

[order_id]     [index_1]
600020001      0
600020002      0
600020002      0
600020002      0
600020003      0
...

which needs to be updated to: -

需要更新到： -

[order_id]     [index_1]
600020001      1
600020002      1
600020002      2
600020002      3
600020003      1  

I am trying to write an UPDATE statement that will populate the index_1 field, as per the example above. I can acheive this using a CURSOR, but ideally would like to do it without if possible.

我正在尝试编写一个UPDATE语句，该语句将填充index_1字段，如上例所示。我可以使用CURSOR来实现这一点，但理想情况下我希望尽可能不这样做。

For each new order_id the numbering restarts. For each order_id row the index_1 field is incremented by 1.

对于每个新的order_id，编号重新开始。对于每个order_id行，index_1字段增加1。

Is it possible to do this without a cursor?

没有游标可以做到这一点吗？

1 个解决方案

#1

You can use a CTE and row_number() to do what you want. The table @T in the code below is only for demonstration. Replace @T with whatever your table is called.

您可以使用CTE和row_number（）来执行您想要的操作。下面代码中的表@T仅用于演示。将@T替换为您调用的表。

declare @T table ([order_id] int, [index_1] int)

insert into @T values
(600020001,      0),
(600020002,      0),
(600020002,      0),
(600020002,      0),
(600020003,      0)

;with cte as
(
  select index_1,
         row_number() over(partition by order_id order by (select 1)) as rn
  from @T       
)
update cte 
  set index_1 = rn

select *
from @T

Result:

结果：

order_id    index_1
----------- -----------
600020001   1
600020002   1
600020002   2
600020002   3
600020003   1