具有排名函数的递归 cte

Question

如何在递归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

为什么没有排名？请告诉我我的错误

Answer 1

编辑

当您阅读有关递归的 CTE 文档时，您会注意到它有一些限制，例如无法使用子查询、group-by、top。这些都涉及多行。通过有限的测试、检查执行计划以及测试这个查询，

with cte as (
  select 1 a, 1 b union all select 1, 2 union all select 1, 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 r.a, cte.b, cast(ROW_NUMBER() over (order by r.b) as int), r.d+1
  from rcte r inner join cte on cte.a=r.a
  where r.d < 2
)
select * 
from rcte
where d=2
order by a, b

我只能得出结论：

1. Row_Number() 在 CTE 中确实有效，当其他表连接起来产生多行结果集时
2. 从编号的结果来看，它很明显，CTE 在所有迭代中都是在单行中处理的，是逐行处理的，而不是多行处理的，尽管它看起来是同时迭代所有行。这可以解释为什么递归 CTE 不允许任何适用于多行操作的函数。

虽然我很容易得出这个结论，但有人显然花了更多的时间以令人难以忍受的细节进行解释 仅 17 个月前...

换句话说，这是SQL Server 实现递归 CTE 的本质，因此窗口函数不会按您期望的方式工作。

---

For the benefit of others, the output is:

a           b           c           d
----------- ----------- ----------- -----------
1           1           1           2
1           2           1           2
2           3           1           2
2           4           1           2

而您期望 c 包含 1,2,1,2 而不是 1,1,1,1。 这看起来确实可能是一个错误，因为没有文档表明窗口函数不应在 CTE 的递归部分中工作。

注意：row_number() 返回bigint，因此您可以仅将anchor(c) 转换为bigint。

由于每次迭代都会增加 d，因此您可以在外部执行窗口化。

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, d) as (
  select a, b, 1 
  from cte
  union all
  select a, b, d+1
  from rcte
  where d < 2
)
select a,b, ROW_NUMBER() over (partition by a,d order by b) c,d
from rcte
--where d=2
order by d, a, b

---

编辑-洞察力

同时回答另一个问题< super>link，我又玩了一些递归 CTE。如果您在没有最终 ORDER BY 的情况下运行它，您可以看到 SQL Server 如何接近递归。有趣的是，在这种情况下它会向后退，然后对每一行进行完整的深度优先递归。

示例表

create table Testdata(SomeID int, OtherID int, Data varchar(max))
insert Testdata select 1, 9, '18,20,22,alpha,beta,gamma,delta'
insert Testdata select 2, 6, ''
insert Testdata select 3, 8, '11,12,.'
insert Testdata select 4, 7, '13,19,20,66,12,232,1232,12312,1312,abc,def'
insert Testdata select 5, 8, '17,19'

递归查询

;with tmp(SomeID, OtherID, DataItem, Data) as (
select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from Testdata
union all
select SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
)
select SomeID, OtherID, DataItem, Data
from tmp
-- order by SomeID

输出显示在迭代第一次中处理的 CTE 锚点，然后由于某种原因锚集中的每一行在处理其他行之前都会递归完成（深度优先）。

但它确实有其奇怪的用途，如这个答案显示