使用CTE与临时表结果不同的原因及CTE替代方案咨询

一、异常原因解析 #

核心问题就是CTE中的随机数表达式会被多次计算。SQL Server里的CTE是逻辑层面的表达式，并非物理存储对象，每次引用CTE时，SQL都会重新执行其定义的逻辑。当你用NEWID()生成随机数时，在连接#numbers和CTE的过程中，max_nr会被反复计算，同一个id_col对应的max_nr在连接的不同阶段可能不一样，自然就会出现数字断层（比如第一次计算max_nr是4，匹配到数字3，第二次计算变成2，数字4就被过滤掉）。

并行处理会加重这个问题：如果SQL Server用并行执行计划处理连接，不同线程可能会分别计算随机数，同一个id_col在不同线程里得到的max_nr不一致，结果就会出现缺失或混乱。而临时表是物理存储的，max_nr一旦生成就固定，后续连接不会再重新计算，所以结果稳定。

二、替代临时表的CTE解决方案 #

要让CTE里的随机数固定，关键是让随机数只计算一次并被物化，以下是几种可行方法：

1. 用OFFSET 0 ROWS强制物化（SQL Server 2012+，推荐） #

OFFSET 0 ROWS是可靠的强制物化手段，优化器不会忽略这个逻辑，能保证随机数只生成一次：

WITH max_cte AS (
    SELECT
        id_col,
        ABS(CHECKSUM(NEWID())) % (5 - 2 + 1) + 2 AS max_nr
    FROM #numbers
    ORDER BY id_col
    OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY -- 对应原临时表生成3行的逻辑
)
SELECT m.id_col, n.number
FROM max_cte m
JOIN #numbers n ON n.number <= m.max_nr

2. 用CROSS APPLY固定每个行的随机数 #

把随机数生成逻辑放到CROSS APPLY里，确保每个id_col只计算一次随机数：

WITH max_cte AS (
    SELECT
        id_col,
        r.max_nr
    FROM #numbers
    CROSS APPLY (
        SELECT ABS(CHECKSUM(NEWID())) % (5 - 2 + 1) + 2 AS max_nr
    ) r
    WHERE id_col <= 3 -- 取3行，匹配原临时表逻辑
)
SELECT m.id_col, n.number
FROM max_cte m
JOIN #numbers n ON n.number <= m.max_nr

3. 用TOP (100) PERCENT加排序强制物化（兼容旧版本） #

虽然SQL优化器可能忽略TOP (100) PERCENT，但配合排序可以触发物化，让随机数固定：

WITH max_cte AS (
    SELECT TOP (100) PERCENT
        id_col,
        ABS(CHECKSUM(NEWID())) % (5 - 2 + 1) + 2 AS max_nr
    FROM #numbers
    WHERE id_col <= 3
    ORDER BY id_col -- 无意义排序，用于触发物化
)
SELECT m.id_col, n.number
FROM max_cte m
JOIN #numbers n ON n.number <= m.max_nr

内容的提问来源于stack exchange，提问作者thothal