我们如何编写可以生成“ Sequence_Code”的SQL函数，例如AA，AB，AC .... AZ。BA，BB，BC ....以及每个“ ID”的后两位数字“ Current_Year”的组合。按“ Record_Date”排序
例如：如果第一行的Current_Year是2019，则Sequence_Code应该是19AA。我的表是LoadData
预期输出为：
带有ROW_NUMBER()窗口函数和数学运算：
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表的列：
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
