问题描述
UPDATE CattleProds
SET SheepTherapy=(ROUND((RAND()* 10000),0))
WHERE SheepTherapy IS NULL
如果我然后执行 SELECT，我会看到我的随机数每一行都相同.任何想法如何生成唯一的随机数?
If I then do a SELECT I see that my random number is identical in every row. Any ideas how to generate unique random numbers?
推荐答案
代替 rand()，使用 newid()，它为结果.通常的方法是使用校验和的模.请注意，checksum(newid()) 可以产生 -2,147,483,648 并导致 abs() 上的整数溢出，因此我们需要对校验和返回值使用模数，然后再将其转换为绝对值.
Instead of rand(), use newid(), which is recalculated for each row in the result. The usual way is to use the modulo of the checksum. Note that checksum(newid()) can produce -2,147,483,648 and cause integer overflow on abs(), so we need to use modulo on the checksum return value before converting it to absolute value.
UPDATE CattleProds
SET SheepTherapy = abs(checksum(NewId()) % 10000)
WHERE SheepTherapy IS NULL
这会生成一个 0 到 9999 之间的随机数.
This generates a random number between 0 and 9999.
这篇关于如何在 SQL 中用随机数填充一列?我在每一行都得到相同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！