SQL多列随机数生成中整合唯一性校验的实现咨询

游标实现方案（针对你的需求） #

首先创建存储结果的表，同时用主键约束自动避免重复组，省去手动校验组是否存在的步骤：

CREATE TABLE RandomNumbers (
    Number1 INT,
    Number2 INT,
    Number3 INT,
    Number4 INT,
    Number5 INT,
    Number6 INT,
    Number7 INT,
    Number8 INT,
    PRIMARY KEY (Number1, Number2, Number3, Number4, Number5, Number6, Number7, Number8)
);

然后编写存储过程，用循环配合校验逻辑（纯游标更适合遍历已有数据，生成数据用WHILE循环更直接）：

CREATE PROCEDURE GenerateValidRandomNumbers
    @TargetCount INT = 1000000
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @CurrentCount INT = 0;
    DECLARE @n1 INT, @n2 INT, @n3 INT, @n4 INT, @n5 INT, @n6 INT, @n7 INT, @n8 INT;
    DECLARE @IsValid BIT;

    WHILE @CurrentCount < @TargetCount
    BEGIN
        -- 生成一组随机数
        SELECT 
            @n1 = ABS(CHECKSUM(NEWID()))%25 + 1,
            @n2 = ABS(CHECKSUM(NEWID()))%25 + 1,
            @n3 = ABS(CHECKSUM(NEWID()))%25 + 1,
            @n4 = ABS(CHECKSUM(NEWID()))%25 + 1,
            @n5 = ABS(CHECKSUM(NEWID()))%25 + 1,
            @n6 = ABS(CHECKSUM(NEWID()))%25 + 1,
            @n7 = ABS(CHECKSUM(NEWID()))%25 + 1,
            @n8 = ABS(CHECKSUM(NEWID()))%10 + 1;

        -- 校验前7列是否无重复：将7列转为行后去重计数，等于7则说明无重复
        SELECT @IsValid = CASE 
            WHEN (SELECT COUNT(DISTINCT val) FROM (VALUES(@n1),(@n2),(@n3),(@n4),(@n5),(@n6),(@n7)) AS v(val)) = 7 
            THEN 1 ELSE 0 END;

        IF @IsValid = 1
        BEGIN
            -- 尝试插入，主键冲突则跳过重复组
            BEGIN TRY
                INSERT INTO RandomNumbers VALUES (@n1, @n2, @n3, @n4, @n5, @n6, @n7, @n8);
                SET @CurrentCount += 1;
            END TRY
            BEGIN CATCH
                CONTINUE;
            END CATCH
        END
    END
END;

调用方式：EXEC GenerateValidRandomNumbers @TargetCount = 1000000;

重要提醒：别用游标/循环生成百万级数据 #

逐行生成+校验+插入的效率极低，百万条数据可能需要数小时才能完成。推荐用批量生成+过滤的集合式方案，效率提升几十倍：

-- 先创建目标表（同上）
CREATE TABLE RandomNumbers (
    Number1 INT,
    Number2 INT,
    Number3 INT,
    Number4 INT,
    Number5 INT,
    Number6 INT,
    Number7 INT,
    Number8 INT,
    PRIMARY KEY (Number1, Number2, Number3, Number4, Number5, Number6, Number7, Number8)
);

-- 批量生成直到达到目标数量
WHILE (SELECT COUNT(*) FROM RandomNumbers) < 1000000
BEGIN
    -- 一次性生成20万条候选数据（数量可根据服务器性能调整），过滤后插入
    INSERT INTO RandomNumbers WITH (IGNORE_DUP_KEY = ON) -- SQL Server用此参数忽略重复主键
    SELECT TOP (100000) -- 每次插入10万有效数据
        n1, n2, n3, n4, n5, n6, n7, n8
    FROM (
        SELECT
            ABS(CHECKSUM(NEWID()))%25 + 1 AS n1,
            ABS(CHECKSUM(NEWID()))%25 + 1 AS n2,
            ABS(CHECKSUM(NEWID()))%25 + 1 AS n3,
            ABS(CHECKSUM(NEWID()))%25 + 1 AS n4,
            ABS(CHECKSUM(NEWID()))%25 + 1 AS n5,
            ABS(CHECKSUM(NEWID()))%25 + 1 AS n6,
            ABS(CHECKSUM(NEWID()))%25 + 1 AS n7,
            ABS(CHECKSUM(NEWID()))%10 + 1 AS n8
        FROM GENERATE_SERIES(1, 200000)
    ) AS candidates
    WHERE (SELECT COUNT(DISTINCT val) FROM (VALUES(n1),(n2),(n3),(n4),(n5),(n6),(n7)) AS v(val)) = 7;
END

这个方案先批量生成大量候选数据，一次性过滤掉前7列有重复的记录，再利用IGNORE_DUP_KEY自动跳过重复组，执行速度远快于游标方案。

内容的提问来源于stack exchange，提问作者AlwaysThatGuy