我有一些sql代码使用以下技术生成随机数：
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
我的问题是这个数字生成是多么随机？还有另一种方法可以做到这一点,更“随机”.
我在用：
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我不能有重复的数字！
解决方法
我想你可以做更简单,更容易的事情
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())
为了获得随机数而不重复,我认为这将完成这项工作
;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
