sql - 使用 SQL 确保随机数不相同

Question

我有以下内容将返回数据库中的 100k 行，这应该会为我找到 6 个随机值。问题是在 Lotto 中你不能有多个相同的值。我注意到有些值显示相同，例如..

21-21-34-3-14-46

很明显，21 和 21 不是 Lotto 彩票中的有效数字。所以我的问题是如何在生成随机值时检查没有数字相同？

这是我的代码:

SET NOCOUNT ON
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=500000)
BEGIN

--Do Stuff


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
SELECT @Random1 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random2 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random3 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random4 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random5 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random6 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
--SELECT @Random;

INSERT INTO CustomerSelections
           ([draw_date]
           ,[val1]
           ,[val2]
           ,[val3]
           ,[val4]
           ,[val5]
           ,[val6])
     VALUES
           (
           '2013-07-05'
           ,@Random1
           ,@Random2
           ,@Random3
           ,@Random4
           ,@Random5
           ,@Random6 
           )


SET @intFlag = @intFlag + 1
END
GO

@戈登:

Answer 1

当我想到没有重复的随机数时，我想到的是“排列”。因此，我们的想法是生成一组可能的值，对它们进行随机排序，然后选择前六个值作为您的集合:

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;