生成1千万个随机串号9位英文字母？
请问一下，在SQL中怎么来做可以快速生成1千万个9位英文（大写）随机不重复的号码，非常着急，谢谢啦。
[解决办法]
建议先按规律生成,保证不重复
再用newid()排序,将其打乱
[解决办法]
1000万?
其实随机不随机已经没有多大区别了，只要满足不重复即可。
多个循环嵌套处理。
[解决办法]
比較困難，要用到循環
[解决办法]
declare @i int
declare @sql varchar(1000)
set @i=0
while @i <9
begin
set @sql= 'create table [a '+ltrim(@i)+ '](a char(1))
declare @s int
set @s=65
while @s <91
begin
insert [a '+ltrim(@i)+ '] select char(@s)
set @s=@s+1
end
'
exec(@sql)
set @i=@i+1
end
select top 10000000 a0.a+a1.a+a2.a+a3.a+a4.a+a5.a+a6.a+a7.a+a8.a a into #yyy from a0,a1,a2,a3,a4,a5,a6,a7,a8 where
a0.a <> a1.a and a0.a <> a2.a and a0.a <> a3.a and a0.a <> a4.a and a0.a <> a5.a and a0.a <> a6.a and a0.a <> a7.a and a0.a <> a8.a and
a1.a <> a2.a and a1.a <> a3.a and a1.a <> a4.a and a1.a <> a5.a and a1.a <> a6.a and a1.a <> a7.a and a1.a <> a8.a and
a2.a <> a3.a and a2.a <> a4.a and a2.a <> a5.a and a2.a <> a6.a and a2.a <> a7.a and a2.a <> a8.a and
a3.a <> a4.a and a3.a <> a5.a and a3.a <> a6.a and a3.a <> a7.a and a3.a <> a8.a and
a4.a <> a5.a and a4.a <> a6.a and a4.a <> a7.a and a4.a <> a8.a and
a5.a <> a6.a and a5.a <> a7.a and a5.a <> a8.a and
a6.a <> a7.a and a6.a <> a8.a and
a7.a <> a8.a
order by newid()
select * from #yyy
drop table #yyy
[解决办法]
环境：sqlserver默认安装
内存：512M
CPU：P4 2.0G
形成随机串花时:
去掉order by newid() 时：将近三分钟
加order by newid() :10分钟未出结果
[解决办法]
DROP TABLE #A
DROP TABLE #B
declare @zz char(9),@ZZ1 CHAR(1),@i int,@J INT,@record int
CREATE TABLE #B(ZMB CHAR(9))
create table #a(id int,zm char(1))
insert into #a select 1, 'A '
UNION SELECT 2, 'B '
UNION SELECT 3, 'C '
UNION SELECT 4, 'D '
UNION SELECT 5, 'E '
UNION SELECT 6, 'F '
UNION SELECT 7, 'G '
UNION SELECT 8, 'H '
UNION SELECT 9, 'I '
UNION SELECT 10, 'J '
UNION SELECT 11, 'K '
UNION SELECT 12, 'L '
UNION SELECT 13, 'M '
UNION SELECT 14, 'N '
UNION SELECT 15, 'O '
UNION SELECT 16, 'P '
UNION SELECT 17, 'Q '
UNION SELECT 18, 'R '
UNION SELECT 19, 'S '
UNION SELECT 20, 'T '
UNION SELECT 21, 'U '
UNION SELECT 22, 'V '
UNION SELECT 23, 'W '
UNION SELECT 24, 'X '
UNION SELECT 25, 'Y '
UNION SELECT 26, 'Z '
--SELECT CHAR(68)
SET @i=0
SET @J=0
SET @ZZ= ' '
WHILE @J <1000
BEGIN
WHILE @I <=9
begin
select @ZZ1=ZM FROM #A WHERE ID=CEILING(rand()*26)
SET @ZZ=RTRIM(@ZZ1)+@ZZ
SET @i=@i+1
IF @I> 9
BEGIN
Select @Record=Count(*) From #B Where ZMB=@ZZ
IF @Record=0
begin
INSERT INTO #B (ZMB)VALUES(@ZZ)
SET @J=@J+1
end
IF @J> =1000
BREAK
SET @I=0
END
end
END
SELECT * FROM #B
[解决办法]
DECLARE @i int
CREATE TABLE #AZ(X nchar(1))
SET @i=65
WHILE @i <=90
BEGIN
INSERT INTO #AZ SELECT CHAR(@i)
SET @i=@i+1
END
SELECT TOP 10000000
A.X+B.X+C.X+D.X+E.X+F.X+G.X+.H.X+I.X AS X INTO #T1
FROM
(SELECT TOP 32 X FROM #AZ ORDER BY NEWID()DESC) AS A
,(SELECT TOP 32 X FROM #AZ ORDER BY NEWID())AS B
,(SELECT TOP 32 X FROM #AZ ORDER BY NEWID()DESC) AS C
,(SELECT TOP 32 X FROM #AZ ORDER BY NEWID()) AS D
,(SELECT TOP 32 X FROM #AZ ORDER BY NEWID()DESC) AS E
,(SELECT TOP 32 X FROM #AZ ORDER BY NEWID()) AS F
,(SELECT TOP 32 X FROM #AZ ORDER BY NEWID()DESC) AS G
,(SELECT TOP 32 X FROM #AZ ORDER BY NEWID()) AS H
,(SELECT TOP 32 X FROM #AZ ORDER BY NEWID() DESC) AS I
WHERE A.X <> B.X AND A.X <> C.X AND A.X <> D.X AND A.X <> E.X AND A.X <> F.X AND A.X <> G.X AND A.X <> H.X AND A.X <> I.X
AND B.X <> C.X AND B.X <> D.X AND B.X <> E.X AND B.X <> F.X AND B.X <> G.X AND B.X <> H.X AND B.X <> I.X
AND C.X <> D.X AND C.X <> E.X AND C.X <> F.X AND C.X <> G.X AND C.X <> H.X AND C.X <> I.X
AND D.X <> E.X AND D.X <> F.X AND D.X <> G.X AND D.X <> H.X AND D.X <> I.X
AND E.X <> F.X AND E.X <> G.X AND E.X <> H.X AND E.X <> I.X
AND F.X <> G.X AND F.X <> H.X AND F.X <> I.X
AND G.X <> H.X AND G.X <> I.X
AND H.X <> I.X
--执行使用 1分20秒
PRINT GETDATE()
--查询数据未测，因为电脑慢，呵呵
--SELECT * FROM #T1
PRINT GETDATE()
DROP TABLE #AZ,#T1
[解决办法]
随机显示可以使用NEWID()，这样避免查询慢。
方法就这样。不知道还有更快的方法，希望大家想，一起学习！
[解决办法]
O_o
