生成1千万个随机串号9位英文字母
原来问题:
请问一下,在SQL中怎么来做可以快速生成1千万个9位英文(大写)随机不重复的号码,非常着急,谢谢啦。
自己写的一个解决方法:
要是哪位朋友想到更好的方法可以写出,大家一起分享!
请问一下,在SQL中怎么来做可以快速生成1千万个9位英文(大写)随机不重复的号码,非常着急,谢谢啦。
自己写的一个解决方法:
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
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
要是哪位朋友想到更好的方法可以写出,大家一起分享!