如何在递归cte中使用排名函数?这是一个简单的例子,显示了我正在尝试做的事情:
with cte as (
select 1 a, 1 b union all select 1, 2 union all select 2, 3 union all select 2, 4
)
, rcte (a, b, c, d) as (
select a, b, cast(0 as int), 1
from cte
union all
select a, b, cast(ROW_NUMBER() over (partition by a order by b) as int), d+1
from rcte
where d < 2
)
select *
from rcte
where d=2
order by a, b
为什么没有排名?告诉我我的错误
1 回答
编辑
当您阅读有关递归的CTE文档时,您会注意到它有一些限制,例如无法使用子查询,分组,顶部 . 这些都涉及多行 . 从有限的测试,检查执行计划,以及测试此查询
我只能得出结论:
Row_Number()在CTE中工作,当其他表连接以生成多行结果集时
从编号结果可以看出,CTE在所有迭代中逐行处理,逐行而不是逐行多次处理,即使它似乎同时迭代所有行 . 这可以解释为什么不允许任何适用于多行操作的函数用于递归CTE .
虽然我很容易得出这个结论,但是显然只有17个月前有人花了很多时间来讨论......
In other words, this is the nature of SQL Server's implementation of the recursive CTE, so windowing functions will not work the way you expect it to.
为了他人的利益,产出是:
而你期望c包含1,2,1,2而不是1,1,1,1 . 这当然看起来可能是一个错误,因为没有文档说窗口函数不应该在CTE的递归部分中起作用 .
注意:row_number()返回bigint,因此您只能将锚点(c)强制转换为bigint .
由于每次迭代增加d,您可以在外面执行窗口化 .
编辑 - 洞察力
在回答another question链接时,我使用递归CTE播放了更多内容 . 如果在没有最终ORDER BY的情况下运行它,您可以看到SQL Server如何接近递归 . 有趣的是,在这种情况下它会倒退,然后在每一行上进行完全深度优先递归 .
样本表
递归查询
输出显示在迭代1中处理的CTE锚点,然后无论出于何种原因,在处理其他行之前,锚点集合中的每一行都被递归到完成(深度优先) .
Yet it does have its strange uses, as this answer shows