在 T-SQL 中生成新的唯一随机数列表 [英] Generate list of new unique random numbers in T-SQL

问题描述

我需要一个存储过程来生成 @n 记录，每条记录都有一个唯一的随机 8 位数字.这个数字不能是递增的，并且不能已经存在于表中.

I need a stored procedure to generate @n records, each with a unique random 8 digit number. This number must not be incremental and must not exist already in the table.

CREATE TABLE Codes
(
    ID UNIQUEIDENTIFIER PRIMARY KEY,
    Code INT,
    CONSTRAINT UQ_Code UNIQUE(Code) 
);

我可以生成随机数:

DECLARE @min int = 0,
        @max int = 99999999,
        @n INT = 100;

SELECT TOP (@n) FLOOR(CAST(CRYPT_GEN_RANDOM(4) AS BIGINT) / 4294967296 * ((@max - @min) + 1)) + @min
FROM   sys.all_objects s1 
              CROSS JOIN sys.all_objects s2;

但我正在努力弄清楚如何以原子方式生成和插入 @n 数字到 [Codes] 表中，同时避免冲突.这可以在没有循环的情况下完成吗?

But what I'm struggling to figure out is how to atomically generate and insert @n numbers into the [Codes] table whilst making provision to avoid collisions. Can this be done without a loop?

更新
通过 不能是增量的"，我的意思只是对于 SP 的每次调用，我不希望它返回 "1, 2, 3,4" 或任何其他常见模式.我需要能够使用所有值，这样最终增量值将存在，但会在不同的时间点生成，而不是按顺序生成.

Update
By "must not be incremental" I simply meant that for each call to the SP, I don't want it to return "1, 2, 3, 4" or any other common pattern. I need to be able to consume all values so ultimately incremental values will exist but will be generated at different points in time rather than sequentially.

推荐答案

您可以将 cte 与计算代码一起使用，distinct 并检查该代码是否已存在于您的表中:

You can use cte with calculated codes, distinct and check if the Code already exists in your table:

;with cte_stream as (
    select
        floor(cast(crypt_gen_random(4) as bigint) / 4294967296 * ((@max - @min) + 1)) + @min as Code
    from sys.all_objects as s1 
        cross join sys.all_objects as s2;
)
insert into [Codes]
select distinct top (@n) s.Code
from cte_stream as s
where not exists (select * from [Codes] as c where c.Code = s.Code)

所以 distinct 可以帮助您避免新代码之间的冲突，而 exists 可以帮助您避免与 [Codes] 中现有代码的冲突表，order by newid() 帮助您从新代码中获取随机值

So distinct helps you to avoid collision between new codes and exists help you to avoid collisions with already existing codes in the [Codes] table, and order by newid() helps you to get random values from new codes

这篇关于在 T-SQL 中生成新的唯一随机数列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！