具有全层次结构的SQL递归

Question

这就是我的查询现在看起来的样子：

with allmembers (objectid, parentid, name, parentname, recursion) as 
(
-- anchor elements: where parentid = 25
select objectid, parentid, name, name as parentname, 0 as recursion
from orgs as orgs1 
where parentid = 25 

-- recursion begins here
union all 
select orgs2.objectid, orgs2.parentid, orgs2.name, orgs3.name as parentname, recursion + 1
from orgs as orgs2 
    join allmembers as orgs3 on orgs2.parentid = orgs3.objectid
)
-- we select all the results 
select *
from allmembers 

它从一个列表中选择orgs (组织)，其中父母为25 (这些是“根组织”)，并递归地将它们与所有的子组织连接起来，直到没有剩下的。所以我们得到了一个组织和他们的父母的名单。

我的问题是，我只得到了直接的子女/父母关系：

姓名@父母姓名 销售情况All_Employees 直销销售

在这个过程中失去的是，“直接销售”也是"All_Employees"，间接的成员，通过"Sales“。因此，我宁愿增加以下结果：

姓名@父母姓名 销售情况All_Employees 直销销售 直销业务All_Employees

如何做到这一点？

Answer 1

在不涉及函数的情况下，使用一条物化的路径满足您的需要吗？

create table orgs (objectid int, name varchar(128), parentid int);
insert into orgs values
 (26,'All Employees', 25)
,(27,'Sales', 26)
,(28,'Direct Sales',27);

with allmembers as (
-- anchor elements: where parentid = 25
  select 
      objectid
    , parentid
    , name
    , parentname = convert(varchar(128),'')
    , rootname   = name
    , recursion  = convert(int,0)
    , name_path  = convert(varchar(256),name)
  from orgs  
  where parentid = 25 
  -- recursion begins here
  union all 
  select 
      c.objectid
    , c.parentid
    , c.name
    , parentname = p.name
    , rootname   = p.rootname
    , recursion  = p.recursion + 1
    , name_path  = convert(varchar(256),p.name_path + ' > ' + c.name)
  from orgs as c 
      join allmembers as p on c.parentid = p.objectid
)
-- we select all the results 
select *
from allmembers 

返回：

+----------+----------+---------------+---------------+---------------+-----------+--------------------------------------+
| objectid | parentid |     name      |  parentname   |   rootname    | recursion |              name_path               |
+----------+----------+---------------+---------------+---------------+-----------+--------------------------------------+
|       26 |       25 | All Employees |               | All Employees |         0 | All Employees                        |
|       27 |       26 | Sales         | All Employees | All Employees |         1 | All Employees > Sales                |
|       28 |       27 | Direct Sales  | Sales         | All Employees |         2 | All Employees > Sales > Direct Sales |
+----------+----------+---------------+---------------+---------------+-----------+--------------------------------------+