时间戳

首页 > 解决方案 > 如何获得在 CTE 中生成的随机数不会在 JOIN 中更改？

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

问题描述

问题

我正在使用这种技术#Table_1为 CTE 表中的每一行生成一个随机数。然后，我将 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 的结果。

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

标签： sqlsql-serverrandomcommon-table-expression

解决方案

这似乎可以解决问题：

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.

推荐阅读