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

Question

问题

我正在为表中的每一行生成一个随机数 #Table_1在 CTE 中，使用 this technique .然后，我将在另一个表上加入 CTE 的结果，#Table_2 .而不是为 #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 的结果。

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

Answer 1

这似乎可以解决问题:

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。 GROUP BY因此，第二个 CTE 中的子句强制数据引擎定义 RandomNumber 一个值，以便它可以执行 GROUP BY .