SQL更新记录，每次递增值从1开始 [英] SQL update records with incrementing value starting from 1 each time

问题描述

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

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.

所以，如果我有

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

如何针对此表运行更新语句，以使Dave设置为1，Paul设置为2.我不想使用游标.

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.

推荐答案

排名函数ROW_NUMBER应该可以满足您的需求.您没有提及有关如何分配序列号的任何特定规则，因此我在这里使用名称来完成此操作:

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

这篇关于SQL更新记录，每次递增值从1开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！