sql-server – 具有排名成果的递归cte

如安在递归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,cast(0 as int),1 
  from cte
  union all
  select a,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

为什么没有排名？汇报我我的错误

办理要领

当您阅读有关递归的CTE文档时,您会留意到它有一些限定,譬喻无法行使子查询,分组,顶部.这些都涉及多行.从有限的测试,搜查执行打算,以及测试此查询

with cte as (
  select 1 a,2 union all select 1,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

我只能得出结论：

>当毗连其他表以天生多行功效集时,Row_Number()在CTE中事变
>从编号功效可以看出,CTE在全部迭代中逐行处理赏罚,逐行而不是逐行多行,纵然它好像同时迭代全部行.这可以表明为什么不应承任何合用于多行操纵的函数用于递归CTE.

固然我很轻易得出这个结论,但显然只有17个月前有人花了许多时刻来explain it in excruciating detail …

换句话说,这是SQL Server实现递归CTE的本质,因此窗口函数将不会凭证您祈望的方法事变.

为了他人的好处,产出是：

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.这虽然看起来也许是一个错误,由于没有文档说窗口函数不该该在CTE的递归部门中起浸染.

留意：row_number()返回bigint,因此您可以将锚点(c)逼迫转换为bigint.

因为每次迭代增进d,您可以在表面执行窗口化.

with cte as (
  select 1 a,d+1
  from rcte
  where d < 2
)
select a,ROW_NUMBER() over (partition by a,d order by b) c,d
from rcte
--where d=2
order by d,a,b

编辑 – 洞察力

答复another questionlink时,我用递归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,66,232,1232,12312,1312,abc,def'
insert Testdata select 5,'17,19'

递归查询

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

输出表现在迭代1中处理赏罚的CTE锚点,然后无论出于何种缘故起因,在处理赏罚其他行之前,锚点荟萃中的每一行都被递归到完成(深度优先).

然而它确实有其稀疏的用途,如this answer所示

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!