在SQL Server中按指定规则随机更新Amount列值的问题

需求 #

更新表格每行的Amount列值，替换为满足以下规则的随机数：

• 当TransactionType = 'Deposit'时，随机值需在10美元~20美元之间
• 其他交易类型时，随机值需满足：0.01美元 ≤ 随机值 ≤ MIN(1美元, 原Amount值)

示例：ID=7的行原Amount为0.80美元，随机值需在0.01~0.80美元之间。

此前尝试用RAND、CHECKSUM、NEWID实现，但出现0值且未达到预期效果，环境为SQL Server。

解决方案 #

SQL Server中RAND()在单条UPDATE语句中会返回相同值，因此需要结合NEWID()和CHECKSUM()生成每行独立的随机数，同时通过数学计算严格控制范围，避免0值。

执行以下UPDATE语句（替换YourTableName为实际表名）：

UPDATE YourTableName
SET Amount = 
    CASE
        -- 处理Deposit类型：生成10-20之间的随机金额，保留两位小数
        WHEN TransactionType = 'Deposit' THEN
            ROUND(10 + (20 - 10) * ABS(CHECKSUM(NEWID())) / 2147483647.0, 2)
        -- 处理其他类型：生成0.01到原Amount和1的较小值之间的随机金额
        ELSE
            ROUND(0.01 + (MIN(1.0, Amount) - 0.01) * ABS(CHECKSUM(NEWID())) / 2147483647.0, 2)
    END

代码解释 #

1. ABS(CHECKSUM(NEWID())) / 2147483647.0：生成0_{1之间的独立随机小数。`CHECKSUM(NEWID())`返回范围为`-2^31`到`2^31-1`的整数，取绝对值后除以最大值`2147483647`，得到均匀分布的0}1小数。
2. 范围控制：
   • Deposit类型：通过10 + (20-10)*随机数锁定10~20的范围
   • 其他类型：用MIN(1.0, Amount)确保随机值不超过原Amount和1美元的上限，再通过0.01 + (上限-0.01)*随机数确保最小值为0.01
3. ROUND(..., 2)：保留两位小数，符合金额的常规格式。

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