ROW_NUMBER在CTE中不起作用

我正在玩报告，似乎row_number在递归中不起作用。

! !我简化了这个例子!!

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中选择All并标记第一个记录在下一次迭代中被排除:

;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

我非常希望得到类似的结果，并递归地调用select。不幸的是，这是怀疑有bug的地方:

;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

哦,抱歉。这必须有一个问题格式:所以我的问题是:这是一个特性还是一个缺陷?

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

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
)

p。如果使用max()或min()函数，其工作原理类似…

这是一个有文档记录的行为。因此，它应该被视为一个"功能"。下面是这种情况的文档:https://msdn.microsoft.com/en-us/library/ms175972.aspx

CTE递归部分的解析函数和聚合函数为应用于当前递归级别的集合，而不是该集合参加CTE考试。像ROW_NUMBER这样的函数只对的子集进行操作由当前递归级别传递给它们的数据，而不是全部传递给CTE递归部分的一组数据。更多的信息，参见例k。在递归函数中使用解析函数

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

相关内容

最新更新

热门标签：