如何为TSQL Select中的每一行生成随机数？

本文翻译自：How do I generate random number for each row in a TSQL Select?

I need a different random number for each row in my table. 我表中的每一行都需要一个不同的随机数。 The following seemingly obvious code uses the same random value for each row. 以下看似明显的代码为每行使用相同的随机值。

SELECT table_name, RAND() magic_number 
FROM information_schema.tables 

I'd like to get an INT or a FLOAT out of this. 我想从中获取整数或浮点数。 The rest of the story is I'm going to use this random number to create a random date offset from a known date, eg 1-14 days offset from a start date. 故事的其余部分是我将使用该随机数创建一个相对于已知日期的随机日期偏移量，例如，相对于开始日期的1-14天偏移量。

This is for Microsoft SQL Server 2000. 这是用于Microsoft SQL Server 2000。

---

#1楼

参考：https://stackoom.com/question/4Nt4/如何为TSQL-Select中的每一行生成随机数

---

#2楼

When called multiple times in a single batch, rand() returns the same number. 在一个批次中多次调用时，rand（）返回相同的数字。

I'd suggest using convert( varbinary , newid() ) as the seed argument: 我建议使用convert（ varbinary ， newid() ）作为种子参数：

SELECT table_name, 1.0 + floor(14 * RAND(convert(varbinary, newid()))) magic_number 
FROM information_schema.tables

newid() is guaranteed to return a different value each time it's called, even within the same batch, so using it as a seed will prompt rand() to give a different value each time. 保证newid()每次调用都返回不同的值，即使在同一批处理中也是如此，因此将其用作种子将提示rand（）每次给出不同的值。

Edited to get a random whole number from 1 to 14. 编辑以获取1到14之间的随机整数。

---

#3楼

Take a look at SQL Server - Set based random numbers which has a very detailed explanation. 看一下SQL Server-基于集合的随机数 ，其中有非常详细的说明。

To summarize, the following code generates a random number between 0 and 13 inclusive with a normalized distribution: 总而言之，以下代码生成一个介于0到13之间（含零）的随机数，并具有标准化分布：

ABS(CHECKSUM(NewId())) % 14

To change your range, just change the number at the end of the expression. 要更改范围，只需更改表达式末尾的数字即可。 Be extra careful if you need a range that includes both positive and negative numbers. 如果需要同时包含正数和负数的范围，请格外小心。 If you do it wrong, it's possible to double-count the number 0. 如果输入错误，则可以对数字0进行重复计数。

A small warning for the math nuts in the room: there is a very slight bias in this code. 这是对房间中数学坚果的一个小警告：该代码中有一个非常小的偏差。 CHECKSUM() results in numbers that are uniform across the entire range of the sql Int datatype, or at least as near so as my (the editor) testing can show. CHECKSUM()得出的数字在sql Int数据类型的整个范围内都是统一的，或者至少与我的（编辑器）测试可以显示的数值相近。 However, there will be some bias when CHECKSUM() produces a number at the very top end of that range. 但是，当CHECKSUM（）在该范围的最顶端产生一个数字时，会出现一些偏差。 Any time you get a number between the maximum possible integer and the last exact multiple of the size of your desired range (14 in this case) before that maximum integer, those results are favored over the remaining portion of your range that cannot be produced from that last multiple of 14. 只要您在最大可能整数与所需范围大小的最后一个精确倍数（在这种情况下为14）之间得到一个数字，这些结果就会在范围内无法产生的其余部分受到青睐最后14的倍数。

As an example, imagine the entire range of the Int type is only 19. 19 is the largest possible integer you can hold. 例如，假设Int类型的整个范围仅为19。19是您可以容纳的最大可能整数。 When CHECKSUM() results in 14-19, these correspond to results 0-5. 当CHECKSUM（）结果为14-19时，它们对应于结果0-5。 Those numbers would be heavily favored over 6-13, because CHECKSUM() is twice as likely to generate them. 这些数字将比6-13更受青睐，因为产生CHEKKSUM（）的可能性是其两倍。 It's easier to demonstrate this visually. 在视觉上更容易证明这一点。 Below is the entire possible set of results for our imaginary integer range: 以下是我们虚数范围的所有可能结果集：

Checksum Integer: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Range Result:     0 1 2 3 4 5 6 7 8 9 10 11 12 13  0  1  2  3  4  5

You can see here that there are more chances to produce some numbers than others: bias. 您可以在这里看到产生一些数字的机会比其他数字更大：偏见。 Thankfully, the actual range of the Int type is much larger... so much so that in most cases the bias is nearly undetectable. 值得庆幸的是，Int类型的实际范围要大得多，以至于在大多数情况下几乎无法检测到偏差。 However, it is something to be aware of if you ever find yourself doing this for serious security code. 但是，如果您发现自己这样做是为了获得严格的安全代码，则要意识到这一点。

---

#4楼

select newid() 选择newid（）

or possibly this select binary_checksum(newid()) 或者可能是这个选择binary_checksum（newid（））

---

#5楼

try using a seed value in the RAND(seedInt). 尝试在RAND（seedInt）中使用种子值。 RAND() will only execute once per statement that is why you see the same number each time. RAND（）每个语句仅执行一次，这就是为什么您每次看到的数字都相同的原因。

---

#6楼

If you don't need it to be an integer, but any random unique identifier, you can use newid() 如果不需要整数，而是任何随机唯一标识符，则可以使用newid()

SELECT table_name, newid() magic_number 
FROM information_schema.tables