首页 文章

具有排名功能的递归cte

提问于
浏览
2

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

  • 11

    编辑

    当您阅读有关递归的CTE文档时,您会注意到它有一些限制,例如无法使用子查询,分组,顶部 . 这些都涉及多行 . 从有限的测试,检查执行计划,以及测试此查询

    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
    

    我只能得出结论:

    • 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.


    为了他人的利益,产出是:

    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,因此您只能将锚点(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
    

    编辑 - 洞察力

    在回答another question链接时,我使用递归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
    

    输出显示在迭代1中处理的CTE锚点,然后无论出于何种原因,在处理其他行之前,锚点集合中的每一行都被递归到完成(深度优先) .

    Yet it does have its strange uses, as this answer shows

相关问题