使用 SQL 生成唯一的随机数 [英] Generate unique random numbers using SQL
问题描述
我有一些使用以下技术生成随机数的 SQL 代码:
I have some SQL code which generates random numbers using the following technique:
DECLARE @Random1 INT, @Random2 INT, @Random3 INT, @Random4 INT, @Random5 INT, @Random6 INT, @Upper INT, @Lower INT
---- This will create a random number between 1 and 49
SET @Lower = 1 ---- The lowest random number
SET @Upper = 49; ---- The highest random number
with nums as (
select @lower as n
union all
select nums.n+1
from nums
where nums.n < @Upper
),
randnums as
(select nums.n, ROW_NUMBER() over (order by newid()) as seqnum
from nums
)
select @Random1 = MAX(case when rn.seqnum = 1 then rn.n end),
@Random2 = MAX(case when rn.seqnum = 2 then rn.n end),
@Random3 = MAX(case when rn.seqnum = 3 then rn.n end),
@Random4 = MAX(case when rn.seqnum = 4 then rn.n end),
@Random5 = MAX(case when rn.seqnum = 5 then rn.n end),
@Random6 = MAX(case when rn.seqnum = 6 then rn.n end)
from randnums rn;
select @Random1, @Random2, @Random3, @Random4, @Random5, @Random6
我的问题是这个数字生成的随机性如何?有没有另一种更随机"的方法来做到这一点.
My question is how random is this number generation? and is there another way to do this which is more "random".
我正在使用:
Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64) Aug 22 2012 19:25:47 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
大多数解决方案的问题是你最终会得到这样的值:14,29,8,14,27,27
我不能有重复的数字!
The problem with most solutions is you'll end up with values like this: 14,29,8,14,27,27
I cannot have duplicate numbers!
推荐答案
我想你可以做这样更简单、更容易的事情
I guess you could do something like this much simpler and much easier
DECLARE @Upper INT;
DECLARE @Lower INT;
SET @Lower = 1; /* -- The lowest random number */
SET @Upper = 49; /* -- The highest random number */
SELECT @Lower + CONVERT(INT, (@Upper-@Lower+1)*RAND());
为了获得一个不重复的随机数,这将完成工作
For getting a random number without repetition, this will do the job
WITH CTE
AS
(
SELECT randomNumber, COUNT(1) countOfRandomNumber
FROM (
SELECT ABS(CAST(NEWID() AS binary(6)) %49) + 1 randomNumber
FROM sysobjects
) sample
GROUP BY randomNumber
)
SELECT TOP 5 randomNumber
FROM CTE
ORDER BY newid()
要设置最高限制,您可以将 49 替换为您的最高限制编号.
To set the highest limit, you can replace 49 with your highest limit number.
这篇关于使用 SQL 生成唯一的随机数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!