SQL Server 2019递归公用表表达式(CTE)中分析函数输出异常问题：原因及可行用法咨询

这个问题戳中了SQL Server递归CTE一个容易被忽略的执行细节——窗口函数在递归成员里的行为和我们直觉里的非递归场景完全不一样。让我先拆解一下两个查询结果不同的原因，再给你几个可行的解决办法。

为什么两个查询结果不同？ #

先看你的非递归版本：RecursiveCte1是把初始集RecursiveCte0的4行，和基于RecursiveCte0全量数据计算出的4行新数据做UNION ALL。这里的窗口函数（MIN/SUM/ROW_NUMBER）是直接作用在RecursiveCte0的完整数据集上，自然能得到全局的聚合结果和行号。

但递归CTE的执行逻辑完全不同：SQL Server是迭代式运行递归CTE的：

1. 先执行初始成员，得到第一层级（RecursiveLevel=0）的4行结果；
2. 然后递归成员会以上一层级的结果作为输入，逐行处理每一行，生成下一层级的行；
3. 重复这个过程直到满足终止条件（这里是RecursiveLevel <1，所以只运行一次递归）。

关键问题来了：在递归成员中，窗口函数的作用域并不是整个输入的层级行集，而是当前正在处理的单独一行。这就是为什么你看到：

• MIN(MinVal) OVER (PARTITION BY Uniform)还是原来的MinVal值（因为窗口里只有当前行）；
• SUM(RowNum) OVER (PARTITION BY Uniform)等于当前行的RowNum（同样，窗口只有当前行）；
• ROW_NUMBER() OVER (PARTITION BY Uniform ORDER BY RowNum)每行都是1（每个窗口只有一行，行号自然是1）。

哪怕你用PARTITION BY (SELECT NULL)也没用，因为递归成员里的窗口函数本质上是逐行孤立计算的，没有办法直接引用整个输入层级的行集。

如何在递归CTE中有效使用分析函数？ #

根据你的需求，有两种可靠的解决方案：

方案1：把窗口函数移到递归CTE之外处理 #

最直接的方式是先让递归CTE生成所有层级的原始数据，然后在外层查询中对每个层级的行集应用窗口函数。这样窗口函数就能正确作用在整个层级的行集上：

WITH SourceData (RowNum, Uniform, RowVal) AS (
 SELECT 1, 'A', 'A' UNION ALL
 SELECT 2, 'A', 'B' UNION ALL
 SELECT 3, 'A', 'C' UNION ALL
 SELECT 4, 'A', 'D'
),
RecursiveCte (RowNum, Uniform, RowVal, MinVal, SomeSum, RowNumCalc, RecursiveLevel) AS (
 -- 初始成员保留原始值
 SELECT RowNum, Uniform, RowVal, RowVal, RowNum, CAST(RowNum AS BIGINT), 0 FROM SourceData
 UNION ALL
 -- 递归成员只传递原始值，不计算窗口函数
 SELECT RowNum, Uniform, RowVal, MinVal, RowNum, RowNum, RecursiveLevel + 1
 FROM RecursiveCte
 WHERE RecursiveLevel < 1
)
-- 外层查询按层级分区计算窗口函数
SELECT 
 RowNum, 
 Uniform, 
 RowVal,
 MIN(MinVal) OVER (PARTITION BY Uniform, RecursiveLevel) AS MinVal,
 SUM(SomeSum) OVER (PARTITION BY Uniform, RecursiveLevel) AS SomeSum,
 ROW_NUMBER() OVER (PARTITION BY Uniform, RecursiveLevel ORDER BY RowNum) AS RowNumCalc,
 RecursiveLevel
FROM RecursiveCte 
ORDER BY RecursiveLevel, RowNum;

执行这个查询，你会得到和非递归版本完全一致的结果。

方案2：在递归成员中预先聚合层级数据 #

如果你必须在递归过程中使用层级的全局聚合值（比如后续递归步骤需要依赖这些值），可以用子查询预先计算当前层级的聚合结果，再和递归行关联：

WITH SourceData (RowNum, Uniform, RowVal) AS (
 SELECT 1, 'A', 'A' UNION ALL
 SELECT 2, 'A', 'B' UNION ALL
 SELECT 3, 'A', 'C' UNION ALL
 SELECT 4, 'A', 'D'
),
RecursiveCte (RowNum, Uniform, RowVal, MinVal, SomeSum, RowNumCalc, RecursiveLevel) AS (
 SELECT RowNum, Uniform, RowVal, RowVal, RowNum, CAST(RowNum AS BIGINT), 0 FROM SourceData
 UNION ALL
 SELECT 
 rc.RowNum, 
 rc.Uniform, 
 rc.RowVal,
 agg.GlobalMin, -- 用子查询得到的全局最小值
 agg.GlobalSum, -- 用子查询得到的全局总和
 ROW_NUMBER() OVER (PARTITION BY rc.Uniform ORDER BY rc.RowNum), -- 此时作用域是当前层级的所有行
 rc.RecursiveLevel + 1
 FROM RecursiveCte rc
 -- 子查询计算当前层级的全局聚合
 CROSS JOIN (
     SELECT MIN(MinVal) AS GlobalMin, SUM(RowNum) AS GlobalSum
     FROM RecursiveCte
     WHERE RecursiveLevel = rc.RecursiveLevel
 ) agg
 WHERE rc.RecursiveLevel < 1
)
SELECT * FROM RecursiveCte ORDER BY RecursiveLevel, RowNum;

这里的CROSS JOIN子查询会先计算当前递归层级的全局聚合值，然后把这些值应用到每一行的计算中，同时ROW_NUMBER也能正确基于整个层级的行集生成行号。

总结 #

SQL Server递归CTE的递归成员对窗口函数的支持有限，核心原因是递归成员是逐行处理输入的，窗口函数无法直接作用在整个层级的行集上。优先选择把窗口函数移到外层查询处理，如果必须在递归过程中使用，就用子查询预先聚合层级数据。

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