在记录组内分配唯一 ID [英] Assign Unique ID within groups of records

问题描述

我有一种情况，我需要为一组记录中的每一个添加一个任意的唯一 ID.在下面更容易形象化.

I have a situation where I need to add an arbitrary unique id to each of a group of records. It's easier to visualize this below.

编辑 11:26 est:目前 lineNum 字段有垃圾.这是在 sql server 2000 上运行的.下面的示例是结果应该是什么样子，但实际值并不重要，只要两个组合字段可以用作唯一键，数字就可以了.

Edited 11:26 est: Currently the lineNum field has garbage. This is running on sql server 2000. The sample that follows is what the results should look like but the actual values aren't important, the numbers could anything as long as the two combined fields can be used for a unique key.

OrderID      lineNum
AAA          1
AAA          2
AAA          3
BBB          1
CCC          1
CCC          2

第 num 行的值不重要，但字段只有 4 个字符.这需要在 sql server 存储过程中完成.我以编程方式执行此操作没有问题.

The value of line num is not important, but the field is only 4 characters. This needs to by done in a sql server stored procedure. I have no problem doing it programatically.

推荐答案

假设您使用的是 SQL Server 2005 或更好的版本，您可以使用 Row_Number()

Assuming your using SQL Server 2005 or better you can use Row_Number()

select orderId,
       row_number() over(PARTITION BY orderId ORDER BY orderId) as lineNum
from Order

这篇关于在记录组内分配唯一 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！