sql-server - 递归 CTE 如何消除重复项？

Question

我正在使用 SQL Server 2014 Express 在 AdventureWorks2012 数据库中学习递归 CTE。我想我主要得到以下示例(取自 Beginning T-SQL 3rd Edition)，但我不太明白为什么递归 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 的固有部分吗？

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

Answer 1

不同之处在于，当您填充#orgchart2 时，您将包括#orgchart 中的所有行。因此，现在当您创建 #orgchart3(代表第三级递归)时，您将加入来自 #orgchart 和 #orgchart2 的行。

因此，当您在#orgchart3 中创建第三级时，它与#orgchart 和#orgchart2 中的行都相关，而它应该只与#orgchart2 相关。相反，您的第三级包括比第二级高一级的行，但也比 anchor 级高一级，因此您正在复制行，因为第二级中已经有比 anchor 级高一级的行。

优化器知道不要对递归 CTE 这样做。每一级递归只查看前一级并忽略它之前的所有递归。因此不会创建重复项。

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