我需要在SQL Server 2008中生成一个3个字符的字母数字序列，如下所示：
001,
002,
...,
999,
A01,
A02,
...,
A99,
B01,
B02,
...,
Z99
序列中的下一项将从存储过程中生成并存储在NCHAR(3)表列中。
要获得下一个序列，您可以添加一个Id赞
WITH seq AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY x.alpha + y.number + z.number) AS Id,
CONVERT(nchar(3), x.alpha + y.number + z.number) AS Result
FROM
(
VALUES
('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'),
('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
('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
) y(number),
(
VALUES
('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
) z(number)
WHERE
NOT (NOT x.alpha BETWEEN '1' AND '9' AND y.number = '0' AND z.number = '0')
)
-- Uncomment to see all results
--SELECT * FROM seq
SELECT Result FROM seq WHERE Id = (SELECT Id + 1 FROM seq WHERE Result = 'Z01')
结果
Z02
全部计数= 3573 = 999 +（26 * 99）
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句