sql - 如何获得在 CTE 中生成的随机数而不在 JOIN 中更改？

问题

我正在使用 this technique 为 CTE 中的表 #Table_1 中的每一行生成一个随机数。然后，我将在另一个表 #Table_2 上加入 CTE 的结果。我没有为 #Table_1 中的每一行获取一个随机数，而是为连接中的每个结果行获取一个新的随机数!

CREATE TABLE #Table_1 (Id INT)

CREATE TABLE #Table_2 (MyId INT, ParentId INT)

INSERT INTO #Table_1
VALUES (1), (2), (3)

INSERT INTO #Table_2
VALUES (1, 1), (2, 1), (3, 1), (4, 1), (1, 2), (2, 2), (3, 2), (1, 3)


;WITH RandomCTE AS
(
    SELECT Id, (ABS(CHECKSUM(NewId())) % 5)RandomNumber
    FROM #Table_1
)
SELECT r.Id, t.MyId, r.RandomNumber
FROM RandomCTE r
INNER JOIN #Table_2 t
    ON r.Id = t.ParentId

结果

Id          MyId        RandomNumber
----------- ----------- ------------
1           1           1
1           2           2
1           3           0
1           4           3
2           1           4
2           2           0
2           3           0
3           1           3

想要的结果

Id          MyId        RandomNumber
----------- ----------- ------------
1           1           1
1           2           1
1           3           1
1           4           1
2           1           4
2           2           4
2           3           4
3           1           3

我试过的

我试图通过将随机数转换为 VARCHAR 来模糊优化器生成随机数的逻辑，但这不起作用。

我不想做什么

我想避免使用临时表来存储 CTE 的结果。

如何在不使用临时存储的情况下为表生成随机数并在连接中保留该随机数？

最佳答案

这似乎可以解决问题:

WITH CTE AS(
    SELECT Id, (ABS(CHECKSUM(NewId())) % 5)RandomNumber
    FROM #Table_1),
RandomCTE AS(
    SELECT Id,
           RandomNumber
    FROM CTE
    GROUP BY ID, RandomNumber)
SELECT *
FROM RandomCTE r
INNER JOIN #Table_2 t
    ON r.Id = t.ParentId;

看起来 SQL Server 意识到，在 CTE 之外，RandomNumber 实际上只是 NEWID() 加上一些附加函数( DB<>Fiddle )，因此它仍然为每一行生成一个唯一的 ID。因此，第二个 CTE 中的 GROUP BY 子句强制数据引擎定义 RandomNumber 一个值，以便它可以执行 GROUP BY 。

关于sql - 如何获得在 CTE 中生成的随机数而不在 JOIN 中更改？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55269241/