sql - SQL Server 中的完全递归员工-老板关系

Question

我需要获得直接或间接依赖于一个人的所有员工的姓名。使用本例中的查询(来自 https://rextester.com/WGVRGJ67798 )，

create table employee(
id int not null,
employee varchar(10) not null,
boss int null    
)

insert into employee values
(1,'Anna',null),
(2,'Bob',1),
(3,'Louis',1),
(4,'Sara',2),
(5,'Sophie',2),
(6,'John',4);

with boss as (
     select id, employee, boss, cast(null as varchar(10)) as name
     from employee 
     where boss is null
    
     union all 
    
     select e.id, e.employee, b.id, b.employee
     from employee e 
     join boss b on b.id = e.boss
 )

select * from boss

我可以得到这个结果:

但是，我需要看到这个:

这就像展示一个人与“低于”他或她的所有员工之间的所有可能关系。

Answer 1

你可以颠倒逻辑:你可以从叶子开始走向根，而不是从老板(根)开始走向员工(叶子)。这使您可以随时生成中间关系:

with cte as (
     select e.id, e.employee, e.boss, b.employee name, b.boss new_boss
     from employee e
     left  join employee b on b.id = e.boss
     union all 
     select c.id, c.employee, c.new_boss, e.employee, e.boss
     from cte c 
     join employee e on e.id = c.new_boss
)
select id, employee, boss, name 
from cte
order by id, boss

Demo on DB Fiddle :
身份证 |员工 |老板 |姓名
-: | :------- | ---: | :---
1 |安娜 |空|空值
2 |鲍勃 | 1 |安娜
3 |路易斯 | 1 |安娜
4 |莎拉 | 1 |安娜
4 |莎拉 | 2 |鲍勃
5 |苏菲 | 1 |安娜
5 |苏菲 | 2 |鲍勃
6 |约翰 | 1 |安娜
6 |约翰 | 2 |鲍勃
6 |约翰 | 4 |萨拉