带有ROW_NUMBER()窗口函数和数学运算：
WITH cte AS (SELECT *, ROW_NUMBER() OVER (ORDER BY Record_Date) rn FROM LoadData)
SELECT RIGHT(Current_Year, 2) +
CHAR(ASCII('A') + rn / 26 + CASE rn % 26 WHEN 0 THEN -1 ELSE 0 END) +
CHAR(ASCII('A') - 1 + CASE rn % 26 WHEN 0 THEN 26 ELSE rn % 26 END) Sequence_Code,
ID, Current_Year, Record_Date
FROM cte
ORDER BY rn
如果要更新Sequence_Code表的列：
WITH cte AS (SELECT *, ROW_NUMBER() OVER (ORDER BY Record_Date) rn FROM LoadData)
UPDATE cte
SET Sequence_Code = RIGHT(Current_Year, 2) +
CHAR(ASCII('A') + rn / 26 + CASE rn % 26 WHEN 0 THEN -1 ELSE 0 END) +
CHAR(ASCII('A') - 1 + CASE rn % 26 WHEN 0 THEN 26 ELSE rn % 26 END)
参见演示。 结果：
> Sequence_Code | ID | Current_Year | Record_Date
> :------------ | -----: | -----------: | :----------
> 18AA | 310001 | 2018 | 2018-01-01
> 19AB | 310002 | 2019 | 2019-02-22
> 20AC | 310004 | 2020 | 2020-02-10
> 20AD | 310003 | 2020 | 2020-02-20
602392714
清零编程群