在mysql 8中获取从经理到员工的路径

Question

我对我的数据库课程有一个疑问，希望您能帮助我。下面的表格就是一个例子:

+------------------------------------+
|ID_EMPLOYEE|ID_MANAGER|NAME_EMPLOYEE|
+1          |2         |A            +
|2          |3         |B            |
|3          |4         |C            |
|4          |5         |D            |
|5          |10        |E            |
|6          |8         |F            |
|7          |9         |G            |
|8          |10        |H            |
|9          |10        |I            |
|10         |11        |J            |
+------------------------------------+

问题是当用户选择一个人作为经理，然后选择另一个员工作为她/他的员工，如何打印从经理到员工的路径。例如:当我选择10个人做经理，2个人做员工，结果肯定是这样的:10→5→4→3→2当我选择5作为经理，然后选择4作为员工，结果必须是:5->4

我尝试使用递归，它可以用所有员工到经理的路径来完成，但当我不能解决这个要求时。

提前谢谢你。

Answer 1

我编写了这个递归CTE来解决这个问题，但后来意识到我得到的结果与您想要的结果相反。你写的是经理到员工，但我写的是员工到经理。我要出去赴约，今天可能没时间修改这个，抱歉。但希望这能让你找到正确的方向。

WITH RECURSIVE routes AS (
SELECT    id_manager,
id_employee || '->' || id_manager as route
FROM employees
WHERE id_employee = 2
UNION ALL
SELECT    e.id_manager, 
r.route || '->' || e.id_manager as route
FROM routes r
inner join employees e
on e.id_employee = r.id_manager
)
SELECT r.route
FROM routes r
where r.id_manager = 10

输出:

2->3->4->5->10

编辑：第二个错误，我没有写MySql，而是写Postgres。我的错，我很快就会删掉。

Answer 2

我想感谢@Isolated，这里是我的问题的sql查询:

WITH RECURSIVE
cte ( node, path, lvl )
AS
( SELECT node, cast(node AS char(10000)), 0 as lvl   
FROM (select id_employee as node, id_manager as parent from employees) as bst WHERE node = '10'
union all
SELECT bst.node,  CONCAT ( cte.path, '-->', bst.node ), 1 + lvl
FROM cte JOIN (select id_employee as node, id_manager as parent from employees) as bst ON cte.node = bst.parent
)
SELECT * FROM cte where cte.node = '2'
ORDER BY lvl asc