请问一下,在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
要是哪位朋友想到更好的方法可以写出,大家一起分享!