T-SQL: 用26个字母表示10000条不重复的编号
看到园子里一位朋友用newid()写的
insert into T
select top 10000
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower(left(replace(newid(),
'-', ''), 20)),
'0', 'a'), 1,
'b'), 2, 'c'), 3,
'd'), 4, 'e'), 5,
'f'), 6, 'g'), 7, 'h'), 8, 'i'),
9, 'j') as col
from sys.all_columns ,
sys.all_objects
这里newid()产生一个GUID, 形如"F6BCE480-834B-4FB9-B905-B568B9F9C7A3", (32个字母和数字加4条横线), 理论上是没有重复的. 问题在于, 当把其中的数字转换成字母之后, 产生的字母序列中一个字母可能对应GUID的一个字母, 也可能对应的是一个数字. 换句话说, 当把这个字母序列"翻译"回GUID的时候, 它最多可以对应到2的32次方个不同的GUID, (拿计算器算了下: 4,294,967,296个). 这样先不说出现重复的概率大不大, 至少理论上不能保证说不出现重复的.
其实, 0到10000, 这数字本身就是不重复的, 干脆把他们转换成字母就好了. 这是方法一:
declare @n int
set @n = 0;
while @n<10000
begin
print replace(replace(convert(varchar(5), @n), '0', 'a'), '1', 'b') ---replace('2', 'c')....
set @n = @n+1
end
上面看到的是a, b, c...h, i, j十个字母的组合, 是不是可以用26个字母 当然是可以的. 不妨把十进制转换成"二十六进制": a = 0, b = 1 .... z = 25. 那么, ba = 26. 好, 没问题, 可以这样:
declare @one int, @n int, @res varchar(5)
set @n = 20 -- test
if (@n = 0) set @res = 'a';
else set @res = ''
while @n > 0
begin
set @one = @n%26
set @res = CHAR(@one+97) + @res
set @n = @n/26
end
print @res
上面解决了"二十六进制"的问题. 我们还知道while循环是可以嵌套的, 于是, 可以用"二十六进制"来表示10000个编号. 方法二:
declare @n int
set @n = 0
while @n < 10000
begin
declare @one int, @num int, @res varchar(5)
set @num = @n
if (@num = 0) set @res = 'a'
else set @res = ''
while @num>0
begin
set @one=@num%26
set @res = CHAR(@one+97) + @res
set @num = @num/26
end
print @res
set @n = @n+1
end
另外, SQL SERVER 2008开始提供表值函数:
select ch 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')
) as char_table(ch)
不妨考虑用26个字母作个交叉连接, 26*26*26 = 17576, 三张表足够了. 方法三:
with chars as
( select ch 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')
) as char_table(ch)
)
select a.ch, b.ch, c.ch, (a.ch+b.ch+c.ch) as rn
from chars as a
cross join chars as b
cross join chars as c
-- results
ch ch ch rn
---- ---- ---- ----
a a a aaa
a b a aba
a c a aca
a d a ada
....
可以看到它从第二列开始递增, 我们想让它从第三列开始:
with chars as
( select ch 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')
) as char_table(ch)
),
bc as (select c1.ch as chb, c2.ch as chc from chars as c1 cross join chars as c2),
abc as (select ch as cha, chb, chc from chars cross join bc)
select top 10000 cha, chb, chc, (cha+chb+chc) as rn from abc
-- results
cha c