MSSQL 中唯一的字母数字密钥生成

Question

>我有带有 char（4） 字段的表用作键。将生成该值（最大值 + 1）。但还没有达到最大值（9999）。无法更改列的数据类型，因为它在很多地方使用。

我想出了如下解决方案...

9999 -> A000...A999->B000...B999->Z999 -> ZA00->ZZZZ （zzzz 再过 10 年就可以了）

请告诉我除了做 substring（） 和字符串操作之外，还有没有一种简单的方法来生成它？

当前代码 ：

DECLARE @cle smallint
SELECT @cle = isnull(MAX(CONVERT(smallint,RTRIM(cle))),0) from smnf
INSERT INTO smnf (sup, cle, dsc, exc, dirty) VALUES (@sup, CONVERT(char(4), @cle+1), @dsc, 0, 'A')

Answer 1

SELECT i INTO #t FROM (SELECT '0' i UNION ALL SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3' UNION ALL SELECT '4' UNION ALL SELECT '5' UNION ALL SELECT '6' UNION ALL SELECT '7' UNION ALL SELECT '8' UNION ALL SELECT '9' UNION ALL SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C' UNION ALL SELECT 'D' UNION ALL SELECT 'E' UNION ALL SELECT 'F' UNION ALL SELECT 'G' UNION ALL SELECT 'H' UNION ALL SELECT 'I' UNION ALL SELECT 'J' UNION ALL SELECT 'K' UNION ALL SELECT 'L' UNION ALL SELECT 'M' UNION ALL SELECT 'N' UNION ALL SELECT 'O' UNION ALL SELECT 'P' UNION ALL SELECT 'Q' UNION ALL SELECT 'R' UNION ALL SELECT 'S' UNION ALL SELECT 'T' UNION ALL SELECT 'U' UNION ALL SELECT 'V' UNION ALL SELECT 'W' UNION ALL SELECT 'X' UNION ALL SELECT 'Y' UNION ALL SELECT 'Z') t
CREATE TABLE id_list (id char(4) PRIMARY KEY);
INSERT id_list
SELECT c1.i+c2.i+c3.i+c4.i
FROM #t c1, #t c2, #t c3, #t c4
WHERE c1.i+c2.i+c3.i+c4.i NOT LIKE '[0-9]%[A-Z]%'  --Eliminate alphas in the first 10000 to preserve existing ids.

当您需要下一个 id 时：

DECLARE @curr_id char(4) = '9ZZZ'
DECLARE @next_id char(4)
SELECT TOP 1 @next_id = [id] FROM id_list WHERE [id] > @curr_id
PRINT @next_id

A000