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

我可以得到这个结果：

然而，我需要看到这个：

这就像展示一个人和所有“在他或她下面”的员工之间的所有可能关系。

- d2907

---

如果你在谷歌上搜索 T-SQL 分层查询，你会发现需要使用递归的 CTE 来实现。 - Panagiotis Kanavos

@Larnu 好的，我的问题是如何修改递归 CTE 查询以获取第二个图像中绿色部分。使用CTE查询,我只得到了第一个结果。这是我的疑问。 - d2907

我已经将您链接中的代码添加到您上面发布的问题中。请注意，问题本身应该是完整的，不应过于依赖其他网站（除非某些内容太大无法在此处包含）。这是因为如果外部网站在未来消失，那么您的问题对于后来的读者可能就没有意义了，而他们本可以从答案中得到帮助。因此，引用其他网站是可以的，但应将它们作为补充/有用的来源，而不是必要的信息。 - RBarryYoung

@RBarryYoung 非常感谢您的建议。下次我会记住的。 - d2907

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

DB Fiddle上的演示:

id | 员工 | 上级 | 名字
-: | :------- | ---: | :---
 1 | Anna     | null | null
 2 | Bob      |    1 | Anna
 3 | Louis    |    1 | Anna
 4 | Sara     |    1 | Anna
 4 | Sara     |    2 | Bob 
 5 | Sophie   |    1 | Anna
 5 | Sophie   |    2 | Bob 
 6 | John     |    1 | Anna
 6 | John     |    2 | Bob 
 6 | John     |    4 | Sara

- GMB

---

非常感谢您提供如此清晰直观的解释。除非我漏掉了什么，否则这应该是被选中的解决方案。 - dancow

Answer 2

我喜欢使用hierarchyid来处理这种事情。

use tempdb;
drop table if exists employee;
drop table if exists #e;

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(concat('/', id, '/') as hierarchyid) as h
     from employee 
     where boss is null
    
     union all 
    
     select e.id, e.employee, b.id, 
        cast(concat(b.h.ToString(), e.id, '/') as hierarchyid)
     from employee e 
     join boss b on b.id = e.boss
 )

select *
into #e
from boss

select e.id, e.employee, b.id, b.employee, b.h.ToString()
from #e as e
left join #e as b
    on e.h.IsDescendantOf(b.h) = 1
    and e.id <> b.id;

我基本上采用了你的代码，并做了以下更改：

1. 不再使用递归CTE来跟踪老板，而是构建一个hierarchyid路径，一直延伸到层次结构的根。

2. 将cte的结果放入临时表中

3. 从临时表中选择，使用自连接进行连接，其中连接条件为“内部表的员工概念在外部表的管理链的任何位置”。

请注意，对于连接，我排除了员工报告给自己的情况； 在这种情况下，您不能成为自己的老板（即使IsDescendantOf方法表明可以！）。

- Ben Thul

Answer 3

类似这样的东西。有两个递归。首先，获取h_level，第一个递归表示老板->员工关系。其次，将第一个递归中的每一行视为新递归中的叶节点，以查找直接和间接的层次关系。

数据

drop table if exists Employee;
go
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(id, employee, boss, h_level) as (
     select id, employee, boss, 0
     from employee 
     where boss is null
     union all 
     select e.id, e.employee, b.id, b.h_level+1
     from employee e 
          join boss b on b.id = e.boss),
downlines(id, employee, boss, h_level, d_level) as (
     select id, employee, boss, h_level, 0
     from boss 
     union all 
     select b.id, b.employee, d.id, d.h_level, d.d_level+1
     from boss b 
          join downlines d on d.id = b.boss)
select * 
from downlines
order by h_level, d_level;

输出

id  employee    boss    h_level d_level
1   Anna    NULL0   0
2   Bob     1   0   1
3   Louis   1   0   1
4   Sara    2   0   2
5   Sophie  2   0   2
6   John    4   0   3
2   Bob     1   1   0
3   Louis   1   1   0
4   Sara    2   1   1
5   Sophie  2   1   1
6   John    4   1   2
4   Sara    2   2   0
5   Sophie  2   2   0
6   John    4   2   1
6   John    4   3   0

- SteveC