如何使SQL中row_number()生成的序号在CROSS APPLY后保持不变？

要让ROW_NUMBER()生成的序号持久化，避免在CROSS APPLY操作后被重置，核心思路是先固化ROW_NUMBER()的计算结果，再基于固化后的结果集执行后续操作。以下是几种可行的实现方式：

方法一：使用公共表表达式（CTE） #

先通过CTE生成包含持久化序号的中间结果，再对CTE执行CROSS APPLY：

WITH CTE AS (
    SELECT 
        col1,
        ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1) AS col2
    FROM mytable
)
SELECT 
    CTE.*,
    T.xml_node
FROM CTE
CROSS APPLY col1.nodes('//*:nodename[position()=sql:column("col2")]') AS T(xml_node)

方法二：使用临时表 #

将ROW_NUMBER()的计算结果存入临时表，再基于临时表操作：

-- 创建临时表存储带序号的结果
SELECT 
    col1,
    ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1) AS col2
INTO #TempTable
FROM mytable

-- 基于临时表执行CROSS APPLY
SELECT 
    #TempTable.*,
    T.xml_node
FROM #TempTable
CROSS APPLY col1.nodes('//*:nodename[position()=sql:column("col2")]') AS T(xml_node)

-- 清理临时表
DROP TABLE #TempTable

方法三：使用表变量 #

和临时表逻辑类似，用表变量存储固化后的序号结果：

DECLARE @TempTable TABLE (
    col1 XML,
    col2 INT
)

-- 插入带序号的数据到表变量
INSERT INTO @TempTable
SELECT 
    col1,
    ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1) AS col2
FROM mytable

-- 执行CROSS APPLY操作
SELECT 
    @TempTable.*,
    T.xml_node
FROM @TempTable
CROSS APPLY col1.nodes('//*:nodename[position()=sql:column("col2")]') AS T(xml_node)

原理说明 #

ROW_NUMBER()是非确定性函数，当直接在包含CROSS APPLY的查询中使用时，SQL Server的查询优化器可能会重新计算该函数，导致序号被重置。通过CTE、临时表或表变量预先计算并存储序号，后续操作直接读取已固化的数值，就能避免函数的重复计算，保持序号的原始值。

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