在数据库中做字母和数字编号的对应关系表

sql_server | 2019-03-07 11:20:43 | 阅读 98 次 | 评论(0)

poi在读取execl文件时,数据的列号都是数字的,但是往往我们回写message时需要的是字母的列号,这里提供一个数据库的方式,建立一张数字--字母对应关系表(sqlServer数据库,其他类似),下次提供以java类的方式对数字和字母进行转换

--第一步
WITH seq AS
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY x.alpha + y.alpha + z.alpha) AS Id,
        CONVERT(nchar(3), x.alpha + y.alpha + z.alpha) AS Result
    FROM 
        (
            VALUES 
            ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'), 
            ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'), 
            ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z')
        ) x(alpha),
        (
            VALUES 
            ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'), 
            ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'), 
            ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z')
        ) y(alpha),
        (
            VALUES 
            ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'), 
            ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'), 
            ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z')
        ) z(alpha)
    WHERE
       NOT (NOT x.alpha BETWEEN 'A' AND 'Z')
),
seq2 as(
	SELECT 
        ROW_NUMBER() OVER (ORDER BY x.alpha + y.alpha) AS Id,
        CONVERT(nchar(3), x.alpha + y.alpha) AS Result
    FROM 
        (
            VALUES 
            ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'), 
            ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'), 
            ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z')
        ) x(alpha),
        (
            VALUES 
            ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'), 
            ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'), 
            ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z')
        ) y(alpha)
    WHERE
       NOT (NOT x.alpha BETWEEN 'A' AND 'Z')
) ,
seq3 as(
	SELECT 
        ROW_NUMBER() OVER (ORDER BY x.alpha) AS Id,
        CONVERT(nchar(3), x.alpha) AS Result
    FROM 
        (
            VALUES 
            ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H'), ('I'), ('J'), 
            ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'), 
            ('U'), ('V'), ('W'), ('X'), ('Y'), ('Z')
        ) x(alpha)
    WHERE
       NOT (NOT x.alpha BETWEEN 'A' AND 'Z')
)
insert into id_to_num select * from (
select ROW_NUMBER() over(ORDER BY result) as id,result  from seq3
union all
select ROW_NUMBER() over(ORDER BY result) as id,result from seq2
union all
SELECT ROW_NUMBER() over(ORDER BY result) as id,result FROM seq WHERE result <= 'XFD') aa
--第二步修改数字编号
update id_to_num set execle_rowid = execle_rowid+26 where len(execle_rownum)=2;
update id_to_num set execle_rowid = execle_rowid+703 where len(execle_rownum)=3
--说明:
--如果直接按照字母顺序插入的话,数据库会把A/AA/AAA三个排在相邻的三个位置,
--这个显然不是我们想要的,因此首先按照普通的字母排序分别把三部分数据插入表中,
--然后修改不同分段的编号,其中一位长度的是26个字母,两位长度的是702个字母组以
--这两个数自分别把两位长度的编号加上26和三维长度的编号都加上703,编号就都可以修改为正常的




文章评论,共0条
游客请输入验证码
最新评论