SQL更新记录，每次从1开始递增值

发布于 2024-09-18 12:59:37 字数 386 浏览 5 评论 0原文

我使用单个插入语句将批量记录添加到表中。我希望为每个新批次分配递增的数字，但每次都从 1 开始。

因此，如果我有

Batch    Name    IncementingValue
1        Joe     1
1        Pete    2
1        Andy    3
2        Sue     1
2        Mike    2
2        Steve   3

，然后添加两条记录（使用单个插入语句）：

3        Dave
3        Paul

如何针对该表运行更新语句，以便将 Dave 设置为 1，将 Paul 设置为 2。我不想使用光标。

原文

I am adding batches of records to a table using a single insert statement. I want each new batch to be allocated incrementing numbers, but starting from 1 each time.

So, if I have

Batch    Name    IncementingValue
1        Joe     1
1        Pete    2
1        Andy    3
2        Sue     1
2        Mike    2
2        Steve   3

and I then add two records (using a single insert statement) :

3        Dave
3        Paul

How can I run an update statement against this table so that Dave will be set to 1 and Paul to 2. I don't want to use a cursor.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

鲜肉鲜肉永远不皱 2024-09-25 12:59:37

排名函数 ROW_NUMBER 应该可以满足您的需要。您没有提到有关如何分配序列号的任何具体规则，因此我在这里使用名称完成了它：

INSERT targetTable(Batch,Name,IncementingValue)
SELECT BatchId,
       Name,
       ROW_NUMBER() OVER (ORDER BY Name)
FROM sourceTable

The ranking function ROW_NUMBER should do what you need. You didn't mention any specific rules about how the sequence number should be allocated, so I've done it here using the name:

INSERT targetTable(Batch,Name,IncementingValue)
SELECT BatchId,
       Name,
       ROW_NUMBER() OVER (ORDER BY Name)
FROM sourceTable

回复收藏 0 原文

盛夏尉蓝 2024-09-25 12:59:37

我需要用日期和格式化数字来完成类似的事情。

希望有人会觉得这个例子有用。

    update TEST_TABLE 
    set ref = reference
          from (
                      select 
                          *, 
                          (CONVERT(VARCHAR(10),GETDATE(),12) + RIGHT('0000' + CAST(ROW_NUMBER() OVER (ORDER BY id) AS VARCHAR(4)), 4)) as reference 
                      from TEST_TABLE
                      WHERE 
                    test_table.id > 4
                        and
                    test_table.id < 8 
                ) TEST_TABLE

I needed to accomplish something similar with dates and formatted numbers.

Hopefully, someone will find this example useful.

    update TEST_TABLE 
    set ref = reference
          from (
                      select 
                          *, 
                          (CONVERT(VARCHAR(10),GETDATE(),12) + RIGHT('0000' + CAST(ROW_NUMBER() OVER (ORDER BY id) AS VARCHAR(4)), 4)) as reference 
                      from TEST_TABLE
                      WHERE 
                    test_table.id > 4
                        and
                    test_table.id < 8 
                ) TEST_TABLE

回复收藏 0 原文

~没有更多了~