SQL 添加迭代列

Question

我有这样的表格

| Id | FirstName    | LastName  | UniqueNumber |
|----|--------------|-----------|--------------|
| 1  | Marie        | Roberts   |              |
| 2  | Howard       | Turner    |              |
| 3  | Larry        | Phillips  |              |
| 4  | Mildred      | Foster    |              |

有超过 50k 条记录，我需要根据名字的第一个字母和姓氏的第一个字母填写 UniqueNumber 列，另外我需要添加特定的 4 位数字。例如，看看第一行Marie Roberts，UniqueNumber列应该像这样填写：MR0001，但是当下一条记录以相同的首字母开头时，数字应该是MR0002。现在我有这样的查询：

DECLARE @Iterator int = 1
SELECT Id       
,FirstName
,LastName
,CONCAT(SUBSTRING(FirstName, 1,1), SUBSTRING(LastName, 2,1)) AS [Initials]
,LAG(CONCAT(SUBSTRING(FirstName, 1,1), SUBSTRING(LastName, 2,1))) OVER (order by FirstName, LastName) AS [PreviousInitials]
,CASE
WHEN CONCAT(SUBSTRING(FirstName, 1,1), SUBSTRING(LastName, 2,1)) = LAG(CONCAT(SUBSTRING(FirstName, 1,1), SUBSTRING(LastName, 2,1))) OVER (order by FirstName, LastName) THEN @Iterator+1
ELSE 1
END as [TempNumber]
FROM [TEST].[dbo].[Person] 
order by FirstName, LastName

结果看起来：

| Id    | FirstName | LastName  | Initials      | PreviousInitials      | TempNumber
-----------------------------------------------------------------------------------------
| 94    | Aaron     | Bailey    | AB            | NULL                  | 1
| 193   | Aaron     | Bailey    | AB            | AB                    | 2
| 292   | Aaron     | Bailey    | AB            | AB                    | 2
| 391   | Aaron     | Bailey    | AB            | AB                    | 2
| 490   | Aaron     | Bailey    | AB            | AB                    | 2
| 589   | Aaron     | Bailey    | AB            | AB                    | 2
| 688   | Aaron     | Bailey    | AB            | AB                    | 2
| 787   | Aaron     | Bailey    | AB            | AB                    | 2
| 886   | Aaron     | Bailey    | AB            | AB                    | 2
| 985   | Aaron     | Bailey    | AB            | AB                    | 2
| 938   | Adam      | Russell   | AR            | AB                    | 1
| 740   | Adam      | Russell   | AR            | AR                    | 2

问题出现在案例语句 I 无法递增@Iterator变量。

Answer 1

你会使用row_number()：

select p.*,
(left(firstname) + left(lastname) +
right('0000' +
convert(varchar(255),
row_number() over (partition by left(firstname) + left(lastname) order by id
), 4
)
) as uniquenumber
from [TEST].[dbo].[Person] p

Answer 2

谢谢，Initialy 这个解决方案不起作用，但是当我在最后按名字、姓氏添加顺序时，它按我的预期工作：

select p.*,
(left(p.FirstName, 1) + SUBSTRING(LastName, 2,1) +
right(('0000' +
convert(varchar(255),
row_number() over (partition by left(firstname, 4) + left(lastname, 4) order by id
), 4
)), 4
)) as uniqueUi
from [TEST].[dbo].[Person] p
order by p.FirstName, p.LastName