SQL Server 2019 实现同组统一且在组计数范围内的随机数方案咨询

首先明确两个容易踩的语法和逻辑误区：

• 你提到的ROW_NUMBER() OVER (PARTITION GROUP BY)是语法错误，正确写法为ROW_NUMBER() OVER (PARTITION BY 分组字段 ORDER BY 排序字段)，这个函数返回的是组内行的序号，并非组总计数；真正作为随机数取值上限的组计数，是COUNT(*) OVER (PARTITION BY 分组字段)的返回值，也就是每个分组的总行数。
• 要实现同组所有行返回同一个随机数，不能在每行的计算逻辑里直接调用随机生成函数，否则同组每行都会算出独立的随机值，必须把随机生成逻辑收敛到分组维度，让全组共享同一个随机种子。

场景1：查询时动态计算（无表结构改造，灵活度最高） #

这是最常用的实现方式，完全不需要修改原有表结构，直接编写查询即可匹配你给出的示例效果，核心逻辑分为三步：

1. 通过窗口函数计算每个分组的总行数，作为随机数的取值上限
2. 对每个分组生成固定的随机种子值，保证同组所有行拿到的种子完全一致
3. 将随机种子映射到1~组总行数的整数范围内

参考实现代码（假设原表名为TestData，分组列是[Group]，主键为ID）：

SELECT
    ID,
    [Group],
    -- 计算分组总条数，即随机数的取值上限
    GroupTotal = COUNT(*) OVER (PARTITION BY [Group]),
    -- 计算同组共享的随机数
    RandomNum = ABS(CHECKSUM(
        FIRST_VALUE(NEWID()) OVER (PARTITION BY [Group] ORDER BY ID)
    )) % COUNT(*) OVER (PARTITION BY [Group]) + 1
FROM TestData

代码逻辑说明：

• FIRST_VALUE(NEWID()) OVER (PARTITION BY [Group] ORDER BY ID) 会自动给同一分组下的所有行返回完全相同的NEWID值，从根源上避免同组随机数不统一的问题
• CHECKSUM()将NEWID生成的字符串值转换为整数，搭配ABS()取绝对值规避负数问题，通过取模运算将值限制在0_{组总行数-1的范围，最后加1就得到1}组总行数的整数随机值，完全符合取值范围要求
• 最终输出效果和你给出的示例完全匹配：比如总共有4行的A组，会共享一个1-4范围内的固定随机数；总共有3行的B组，会共享一个1-3范围内的固定随机数。

场景2：表层面持久化存储随机值（受SQL Server规则限制） #

SQL Server 2019 对持久化计算列有严格约束：不允许在计算列定义中使用窗口函数，也不允许调用NEWID()、RAND()这类非确定性函数，因此无法直接在表上创建纯计算列实现该逻辑，需要做轻量调整：

• 如果分组值相对固定，可单独创建分组映射表，存储每个分组预先生成的、匹配该组行数量范围的固定随机值，主表通过关联查询直接取值，无额外计算开销
• 如果不想新增映射表，可通过触发器监听主表的增删改操作，在数据变动时自动计算对应分组的总行数、生成符合范围的随机值，回写到主表的普通存储列上，避免每次查询实时计算

很多人实现时会直接在每行计算ABS(CHECKSUM(NEWID())) % 组总行数 + 1，这种写法会让每一行的NEWID值都不同，最终同组不同行的随机数完全不一致，根本达不到同组同值的效果，必须通过窗口聚合函数把随机种子收敛到分组维度才能保证结果正确。

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