为每一行递增的列，其中列 A = 数字并在 A = 另一个数字时重置为零并再次递增 [英] a column that increments for every row where column A = number and resets to zero when A = another number and increments again

问题描述

我需要一个列 (LineCount)，它会为另一列 (ItemID) 中的每个 ID 实例递增:

I need a column (LineCount) that increments for every instance of an ID in another column (ItemID) for example:

 |ItemID|LineCount|
 ------------------
 | 1    | 1
 ------------------
 | 1    | 2
 ------------------
 | 1    | 3
 ------------------
 | 2    | 1  
  ------------------
 | 2    | 2
 ------------------
 | 2    | 3
 ------------------
 | 2    | 4
 ------------------
 | 2    | 5
  ------------------
 | 3    | 1
 ------------------
 | 3    | 2
  ------------------
 | 3    | 3
 ------------------
 | 3    | 4

SQL 可能类似于:

"Insert into TableA increment LineCount Where ITEMID = @ITEMID"

推荐答案

您的代码看起来像 SQL Server 的.如果是这样，您可以使用窗口函数row_number来生成分区序列号.

Your code looks like SQL Server's. If so, you can use window function row_number to generate partitioned sequence numbers.

insert into tablea
select itemId,
    row_number() over (
        partition by itemId order by itemId
        )
from your_table;

这篇关于为每一行递增的列，其中列 A = 数字并在 A = 另一个数字时重置为零并再次递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！