ROW_NUMBER在CTE中不起作用 [英] ROW_NUMBER is not working in CTE

问题描述

我正在查看报告，看来row_number不能递归工作.

I was playing around reports and it appeared that row_number is not working in recursion.

！我简化了例子！！

从具有3条记录的表中进行

From table with 3 records:

declare @sometable table (id int, id2 int)
insert into @sometable
select 1 as id,   11 as id2   
union all
select 2,         22   
union all
select 3,        33    

在CTE中选择全部"，然后标记要在下一次迭代中排除的第一条记录:

In CTE select All and mark first record to be excluded on the next iteration:

;with cte(iteration, ord, id, id2, deal) as 
(
select ordered.*
    , deal = (case when ord = 1 then 1 else 0 end)
from 
    (select 1 iteration,
        ord = ROW_NUMBER() OVER (ORDER BY id),
        st.*
    FROM @sometable st) ordered

)
select * from CTE
union all
    select 
    ordersinverted.nextIteration,
    ordersinverted.ord,
    ordersinverted.id,              
    ordersinverted.id2,
    deal = (case when ord = 1 then 1 else 0 end)
from (
    select 
        ROW_NUMBER() OVER (PARTITION BY ord ORDER BY iteration desc) as reversedIteration,
        ROW_NUMBER() OVER (ORDER BY cte.id) as ord,
        iteration + 1 as nextIteration,                 
        cte.id, 
        cte.id2                 
    from cte 
    where cte.deal = 0
) ordersinverted

它为我提供了3次迭代的预期结果:在CTE结果中使用row_number

It gives me an expected result for 3 iterations: Use row_number out of CTE result

我非常想得到类似的结果，并将其递归地称为select.不幸的是，这是怀疑存在错误的地方:

I would very much like to get similar result and recursively called select. Unfortunately this is where bug is suspected to have place:

;with cte(iteration, ord, id, id2, deal) as 
(
    select ordered.*
        , deal = (case when ord = 1 then 1 else 0 end)
    from 
        (select 1 iteration,
            ord = ROW_NUMBER() OVER (ORDER BY id),
            st.*
        FROM @sometable st) ordered
union all
    select 
        ordersinverted.nextIteration,
        ordersinverted.ord,
        ordersinverted.id,              
        ordersinverted.id2,
        deal = (case when ord = 1 then 1 else 0 end)
    from (
        select 
            ROW_NUMBER() OVER (PARTITION BY ord ORDER BY iteration desc) as     reversedIteration,
            ROW_NUMBER() OVER (ORDER BY cte.id) as ord,
            iteration + 1 as nextIteration,                 
            cte.id, 
            cte.id2                 
        from cte 
        where cte.deal = 0
    ) ordersinverted
)
select * from CTE

在CTE结果内使用row_number

Use row_number within CTE result

哦，对不起.这必须具有问题格式:所以我的问题是:这是功能还是错误?

Oh, Sorry. This must have a question format: So my Question is: Is this a feature or a bug?

请注意，对Oracle的类似查询将按预期工作:

Please note that similar query for Oracle would work as expected:

with T (id,grp_id) as (
select 1 as id,1 as grp_id from dual union all
select 2 as id,1 as grp_id from dual union all
select 3 as id,1 as grp_id from dual union all
select 1 as id,2 as grp_id from dual union all
select 2 as id,2 as grp_id from dual union all
select 3 as id,2 as grp_id from dual )
,
rec (id,grp_id,rn) as (
 select id, grp_id, row_number()over(partition by grp_id order by id) rn from T where grp_id=1 
 union all
 select t.id, t.grp_id, row_number() over(partition by t.grp_id  order by t.id) rn from T inner join rec on t.id=rec.id and t.grp_id=rec.grp_id+1

)

PS.如果使用max()或min()函数，其工作原理类似.

PS. It works similarly if to use max() or min() functions...

推荐答案

这是已记录的行为.因此，应将其视为功能".这是此情况的文档: https://msdn.microsoft.com/en-us/library/ms175972.aspx

This is a documented behavior. Hence it should be considered as a "feature". Here is the documentation for this case: https://msdn.microsoft.com/en-us/library/ms175972.aspx

CTE的递归部分中的分析函数和集合函数是应用于当前递归级别的集合，而不应用于集合CTE.ROW_NUMBER之类的函数仅在以下子集上起作用当前递归级别而不是整个递归级别传递给他们的数据传递给CTE递归部分的一组数据.欲了解更多信息，请参阅示例K.在递归中使用分析函数随后的CTE.

Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data passed to the recursive part of the CTE. For more information, see example K. Using analytical functions in a recursive CTE that follows.

本文的段落K很好地演示了递归CTE中ROW_NUMBER函数的行为.由于递归CTE逐行处理数据，因此CTE递归部分中的ROW_NUMBER将始终返回1.您可以将 ROW_NUMBER()OVER(ORDER BY id)更改为 COUNT(*)OVER()来检查SQL Server一次为锚点和CTE的递归部分处理的行数.分别是3和1.

Paragraph K of this article has a nice demo for the behavior of ROW_NUMBER function in a recursive CTE. Because recursive CTE process data row-by-row, ROW_NUMBER in the recursive part of the CTE will always return 1. You can change ROW_NUMBER() OVER (ORDER BY id) to COUNT(*) OVER () to check how many rows SQL Server process at once for the anchor and for the recursive part of the CTE. There will be 3 and 1 respectively.

这篇关于ROW_NUMBER在CTE中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！