涉及 RAND(...NEWID()) 的 INNER JOIN 返回意外的行数

Question

我想生成一些具有随机值的样本数据。

我有一个 5 行的迷你表，id 从 1 到 5，每行都有一些文本。

然后我想生成 65536 行 - 第一列每行的值为 1，第二列是 1 到 5 之间的随机数，没有 NULL 值。

然后我想加入这两个表。使用 ROW_NUMBER() % 5 方法 INNER/OUTER JOIN 返回 65536 行。

我想使用由 NEWID 作为种子的 RAND，而不是这个伪随机列。

正如我所怀疑的，LEFT JOIN 返回 65536 行，但 INNER JOIN 为每次调用返回不同的行数。

当LFINAL表具体化为临时表时，INNER JOIN起作用并也返回65536行。

有人可以向我解释为什么 INNER JOIN 没有按我的预期返回 65536 行吗？

WITH Names AS 
(
    SELECT id, row_name 
    FROM 
        (SELECT 1, 'Row 1' UNION ALL 
         SELECT 2, 'Row 2' UNION ALL
         SELECT 3, 'Row 3' UNION ALL
         SELECT 4, 'Row 4' UNION ALL
         SELECT 5, 'Row 5') AS D (id, row_name)
),
L0 AS 
(
    SELECT c 
    FROM 
        (SELECT 1 UNION ALL SELECT 1) AS D(c)
),  --2^1
L1 AS 
(
    SELECT 1 AS c 
    FROM L0 AS A 
    CROSS JOIN L0 AS B
),          --2^2
L2 AS 
(
    SELECT 1 AS c 
    FROM L1 AS A 
    CROSS JOIN L1 AS B
),          --2^4
L3 AS 
(
    SELECT 1 AS c 
    FROM L2 AS A 
    CROSS JOIN L2 AS B
),          --2^8
L4 AS 
(
    SELECT 1 AS c 
    FROM L3 AS A 
    CROSS JOIN L3 AS B
),          --2^16 = 65536
LFINAL AS 
(
    SELECT 
        c, 
        --ROW_NUMBER() OVER (ORDER BY c) % 5 + 1 AS rnd FROM L4)
        FLOOR(RAND(CONVERT(VARBINARY, NEWID()))*5) + 1 AS rnd 
    FROM 
        L4
)
SELECT * 
FROM LFINAL lf
LEFT JOIN Names n ON n.id = lf.rnd

Answer 1

我能够重现该问题。 这应该是预期的行为，因为：

• newid()不是每行生成的，而是每次调用生成的
• 优化器可以自由评估多次一行

测试时，SQL Server 似乎在幕后执行以下操作：

for (row 1 ... 5 in dbo.names)
    for (row 1 ... 65536 in lfinal)
        add the pair {dbo.names.id, rand(newid...) as rnd} to selection
filter (where dbo.names.id = rnd)

请注意，327680 行集中的每一行都有 20% 的概率与过滤器匹配。您将总共获得大约 65536 行，但不完全那么多。

我建议将随机数插入临时表（变量）中，以便数字具体化，然后连接。