本文介绍了有没有办法在SQL中用当前年份生成字母序列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
推荐答案
我们如何编写一个可以生成 'Sequence_Code' 的 SQL 函数,如 AA,AB,AC....AZ.BA,BB,BC.... 与每个ID"的Current_Year"的最后两位数字的组合.按Record_Date"排序
How can we write a SQL function which can generate 'Sequence_Code' like AA,AB,AC....AZ. BA,BB,BC.... with the combination of last two digit of 'Current_Year' for each 'ID'. Order by 'Record_Date'
例如:如果第一行的 Current_Year 是 2019,那么 Sequence_Code 应该是 19AA.我的表是 LoadData
For instance: If Current_Year of First row is is 2019, then Sequence_Code should be 19AA. My table is LoadData
NULL | 310001 | 2019 | 2019-01-01 |
NULL | 310002 | 2018 | 2018-02-22 |
NULL | 310003 | 2020 | 2020-02-20 |
NULL | 310004 | 2020 | 2020-02-10 |
预期输出为:
19AA | 310001 | 2019 | 2019-01-01 |
18AB | 310002 | 2018 | 2018-02-22 |
20AC | 310003 | 2020 | 2020-02-20 |
公元20年 | 310004 | 2020 | 2020-02-10 |
用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)请参阅演示.结果:
See the demo. Results:
> 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更多推荐
有没有办法在SQL中用当前年份生成字母序列
发布评论