首页 文章

递归CTE如何消除重复？

提问于
浏览 次
3

我正在使用SQL Server 2014 Express在AdventureWorks2012数据库中学习递归CTE . 我想我主要得到以下示例（从Beginning T-SQL第3版开始），但我不太明白为什么递归CTE不会产生重复 .

下面是我试图理解的递归CTE，它是一个标准的员工 - 经理层次结构 .

;with orgchart (employeeid, managerid, title, level, node) as (
    --Anchor
    select employeeid
    , managerid
    , title
    , 0
    , convert(varchar(30),'/') 'node'
    from employee
    where managerid is null 
    union all
    --Recursive
    select emp.employeeid
    , emp.managerid
    , emp.title
    , oc.level + 1
    , convert(varchar(30), oc.node + convert(varchar(30),emp.managerid) + '/')
    from employee emp
    inner join orgchart oc on oc.employeeid = emp.managerid 
    )
select employeeid
, managerid
, space(level * 3) + title 'title'
, level
, node
from orgchart
order by node;

它工作正常，但问题来自于我试图通过临时表重新创建它来了解正在发生的事情 . 我创建了一系列临时表，将一个输出插入到下一个查询的输入中，并重新创建递归CTE的功能 .

--Anchor (Level 0)
select employeeid
, managerid
, title
, 0
, convert(varchar(30),'/') 'node'
into #orgchart
from employee
where managerid is null

然后我使用该临时表重新创建第一级递归，此时它只是递归CTE但具有临时表 .

--Anchor + 1 level
select *
into #orgchart2
from #orgchart
union all
select emp.employeeid
, emp.managerid
, emp.title
, oc.level + 1
, convert(varchar(30), oc.node + convert(varchar(30),emp.managerid) + '/') 
from employee emp
inner join #orgchart oc on oc.employeeid = emp.managerid

到目前为止，结果很有意义 . 然后我再一次这样做，但是这里它开始崩溃了：

--Anchor + 2 levels
select *
into #orgchart3
from #orgchart2
union all
select emp.employeeid
, emp.managerid
, emp.title
, oc.level + 1
, convert(varchar(30), oc.node + convert(varchar(30),emp.managerid) + '/')
from employee emp
inner join #orgchart2 oc on oc.employeeid = emp.managerid

此输出开始返回1级员工的重复行（所有字段重复） . 这是有道理的 - UNION ALL之后的第二个查询将返回先前的级别以及新的递归级别，并且UNION ALL不会重复 . 如果我进行另一轮递归，则2级员工也会重复，依此类推 .

我知道我可以将UNION ALL更改为UNION以删除重复项，但我试图理解为什么递归CTE也不会产生重复项？它使用UNION ALL，因此我不明白重复数据删除的来源 . 删除重复项是递归CTE的固有部分吗？

我正在尝试发布所有结果集，但如果他们需要了解问题，请告诉我，我会发布它们 . 提前致谢 .

sql-server tsql recursion duplicates sql-server-2014

1 回答

  • 2

    不同之处在于，当您填充＃orgchart2时，您将包含#orgchart中的所有行 . 所以现在当你创建#orgchart3（代表第三级递归）时，你正在加入来自#orgchart和#orgchart2的行 .

    因此，当您在#orgchart3中创建第三级时，它与#orgchart和#orgchart2中的行相关，而它只应与＃orgchart2相关 . 相反，您的第三级包括超出第二级的一级，但也超出锚级别的一级，因此您复制行，因为您已经在第二级中具有超出锚级别一级的行 .

    优化器知道不要使用递归CTE来做到这一点 . 每个级别的递归只查看前一个递归，并忽略它之前的所有递归 . 因此，不会创建重复项 .

    如果在填充＃orgchart2和＃orgchart3时遗漏了UNION ALL的上半部分，然后最终生成所有三个临时表中的单个UNION ALL，您将模拟优化器的作用 .

    回复于

相关问题