在SQL Server中生成稳定的随机数

Question

我在SQL Server 2012中工作。我正在清理数据，并试图随机打破地址分配算法中的联系。这需要我生成随机数。这个答案所采用的方法不起作用，因为WITH子查询似乎会重新生成联接的每一侧，从而导致同一行出现不同的随机数。

此代码复制了问题：

WITH
initial_table AS (
SELECT 1 AS id
),
random_values_added AS (
SELECT id, ABS(CHECKSUM(NEWID())) % 10 AS rand
FROM initial_table
)
SELECT t1.id, t2,id, t1.rand, t2.rand
FROM random_values_added t1 FULL OUTER JOIN random_values_added t2
ON t1.id = t2.id AND t1.rand = t2.rand

Answer 1

由于CTE是动态运行的，当您调用联接条件时，您可以将中间结果存储在一个临时表中，并利用它进行查询。

WITH
initial_table AS (
SELECT 1 AS id
union all
SELECT 2
),
random_values_added AS (
SELECT id, newid() AS rand
FROM initial_table
)
select * into #RANDTable FROM random_values_added

SELECT t1.id, t2.id, t1.rand, t2.rand
FROM #RANDTable t1 
FULL OUTER JOIN #RANDTable t2
ON t1.id = t2.id AND t1.rand = t2.rand

+----+----+--------------------------------------+--------------------------------------+
| id | id |                 rand                 |                 rand                 |
+----+----+--------------------------------------+--------------------------------------+
|  1 |  1 | 3E366071-CD5A-4B1F-A043-831FE13B7102 | 3E366071-CD5A-4B1F-A043-831FE13B7102 |
|  2 |  2 | CC53C2F8-1C06-4BD8-8111-7133B45F4B79 | CC53C2F8-1C06-4BD8-8111-7133B45F4B79 |
+----+----+--------------------------------------+--------------------------------------+

Answer 2

您可以使用rand()使用技巧。假设id是唯一的，您可以从开始

WITH initial_table AS (
SELECT 1 AS id
),
random_values_added AS (
SELECT id, FLOOR(RAND(id) * 10) AS rand
FROM initial_table
)

这并不完全令人满意，因为每次运行代码时都会返回相同的值。为此，我们可以使用getdate()在每个查询中评估一次的事实：

WITH initial_table AS (
SELECT 1 AS id
),
random_values_added AS (
SELECT id, FLOOR(RAND(DATEADD(second, id, GETDATE()) * 10) AS rand
FROM initial_table
)

Answer 3

您能尝试删除自联接吗？

SELECT id, ABS(CHECKSUM(NEWID())) % 10 AS rand
FROM your_table 

如果rand()函数在SQL Server 2012中工作，您也可以尝试它吗？