Server员工管理器层次结构删除循环引用

Question

我有一个员工名单；每个员工都被分配给一个经理。经理会在任何时候为员工更换。我使用递归CTE来获取经理的员工。

我收到了这个错误

在语句完成之前，最大递归100已经耗尽。

当层次结构是这样的时候：

x  => y
y  => z
z  => x
x  => y

在获得经理层次结构员工时，我希望跳过员工x => y的循环引用。

Answer 1

您的示例数据有点模糊(可能过于简化)。不过，您可以使用递归的CTE遍历图并检测循环。

例如，可以将示例数据创建为：

create table t (
  k int,
  emp_id char(1),
  manager_id char(1)
);

insert into t (k, emp_id, manager_id) values
  (1, 'x', 'y'),
  (2, 'y', 'z'),
  (3, 'z', 'x'),
  (4, 'x', 'y');

然后，从第一行开始遍历并检测循环的查询可以采用表单(参见在db<>fiddle上运行的示例)：

with
n as (
  select k, emp_id, manager_id, 0 as level,
    cast(concat(':', emp_id, ':') as varchar(1000)) as path, 0 as cycle_found
  from t where k = 1
 union all
  select t.k, t.emp_id, t.manager_id, n.level + 1,
    cast(concat(path, t.emp_id, ':') as varchar(1000)),
    sign(charindex(concat(':', t.emp_id, ':'), path))
  from n
  join t on t.emp_id = n.manager_id
    and n.cycle_found = 0
)
select * from n;

结果：

k  emp_id  manager_id  level  path       cycle_found 
-- ------- ----------- ------ ---------- ----------- 
1  x       y           0      :x:        0           
2  y       z           1      :x:y:      0           
3  z       x           2      :x:y:z:    0           
1  x       y           3      :x:y:z:x:  1           
4  x       y           3      :x:y:z:x:  1           

注意，通过在列path中存储所有已遍历的节点来检测循环。还请注意，一旦检测到循环，查询将通过使用条件and n.cycle_found = 0立即停止遍历该路径。