生成1千万个随机串号9位英文字母？

[wwaut]

请问一下，在SQL中怎么来做可以快速生成1千万个9位英文（大写）随机不重复的号码，非常着急，谢谢啦。

[RunUpwind]

建议先按规律生成,保证不重复
再用newid()排序,将其打乱

[OracleRoob]

1000万?

其实随机不随机已经没有多大区别了，只要满足不重复即可。

多个循环嵌套处理。

[fa_ge]

比較困難，要用到循環

[chuifengde]

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

[chuifengde]

环境：sqlserver默认安装
内存：512M
CPU：P4 2.0G
形成随机串花时:
去掉order by newid() 时：将近三分钟
加order by newid() :10分钟未出结果

[millsyys2006]

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

[Andy-W]

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

[Andy-W]

随机显示可以使用NEWID()，这样避免查询慢。
方法就这样。不知道还有更快的方法，希望大家想，一起学习！

[maopi9999]

O_o