备注:测试数据库版本为MySQL 8.0
如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本
一.需求
员工CLARK为KING工作，假设员工CLARK又是另一个员工的经理，怎么办?
select ename,empno,mgr
from emp
where ename in ('KING','CLARK','MILLER');
+--------+-------+------+
| ename | empno | mgr |
+--------+-------+------+
| CLARK | 7782 | 7839 |
| KING | 7839 | NULL |
| MILLER | 7934 | 7782 |
+--------+-------+------+
可以看到，员工MILLER为CLARK工作，而CLARK又为KING工作。
现在需要表示从 MILLER到KING的完整层次。
返回下列结果集:
+-----------------------+
| leaf_branch_root |
+-----------------------+
| MILLER-->CLARK-->KING |
+-----------------------+
二.解决方案
对表EMP进行两次自联接，返回MILLER、MILLER的经理CLARK、CLARK的经理KING.
select concat(a.ename,'-->',b.ename,'-->',c.ename) as leaf_branch_root
from emp a, emp b, emp c
where a.ename = 'MILLER'
and a.mgr = b.empno
and b.mgr = c.empno;
测试记录:
mysql> select concat(a.ename,'-->',b.ename,'-->',c.ename) as leaf_branch_root
-> from emp a, emp b, emp c
-> where a.ename = 'MILLER'
-> and a.mgr = b.empno
-> and b.mgr = c.empno;
+-----------------------+
| leaf_branch_root |
+-----------------------+
| MILLER-->CLARK-->KING |
+-----------------------+
1 row in set (0.00 sec)
