備注:測試數(shù)據(jù)庫版本為MySQL 8.0

如需要scott用戶下建表及錄入數(shù)據(jù)語句液荸，可參考:
scott建表及錄入數(shù)據(jù)sql腳本

一.需求

返回一個結(jié)果集叫编，它描述整個表的層次。
在EMP表中臣嚣，員工KING沒有經(jīng)理娇昙，所以KING是根節(jié)點尺迂。
從KING開始，顯示KING下面的所有員工以及KING下屬的所有員工(如存在)冒掌。

最后噪裕，返回下列結(jié)果集:
+------------------------------+
| emp_tree |
+------------------------------+
| KING |
| KING - BLAKE |
| KING - BLAKE - ALLEN |
| KING - BLAKE - JAMES |
| KING - BLAKE - MARTIN |
| KING - BLAKE - TURNER |
| KING - BLAKE - WARD |
| KING - CLARK |
| KING - CLARK - MILLER |
| KING - JONES |
| KING - JONES - FORD |
| KING - JONES - FORD - SMITH |
| KING - JONES - SCOTT |
| KING - JONES - SCOTT - ADAMS |
+------------------------------+

二.解決方案

2.1 使用union和多個自聯(lián)接

select  emp_tree
  from  (
select  ename as emp_tree
  from  emp
 where  mgr is null
union
select concat(a.ename,' - ',b.ename)
  from emp a
 inner join emp b 
    on a.empno = b.mgr
where   a.mgr is null
union
select  concat(a.ename,' - ',
               b.ename,' - ',c.ename)
  from  emp a
 inner  join emp b
    on  a.empno = b.mgr
  left  join emp c
    on  b.empno = c.mgr
 where  a.ename = 'KING'
union
select  concat(a.ename,' - ',b.ename,' - ',
               c.ename,' - ',d.ename)
  from  emp a
  inner join emp b 
     on a.emono = b.mgr
  inner join emp c
     on b.emono = c.mgr
   left join emp d
     on c.empno = d.mgr
 where  a.ename = 'KING'
    ） x
 where tree is not null
 order by 1;

測試記錄:

mysql> select  emp_tree
    ->   from  (
    -> select  ename as emp_tree
    ->   from  emp
    ->  where  mgr is null
    -> union
    -> select concat(a.ename,' - ',b.ename)
    ->   from emp a
    ->  inner join emp b
    ->     on a.empno = b.mgr
    -> where   a.mgr is null
    -> union
    -> select  concat(a.ename,' - ',
    ->                b.ename,' - ',c.ename)
    ->   from  emp a
    ->  inner  join emp b
    ->     on  a.empno = b.mgr
    ->   left  join emp c
    ->     on  b.empno = c.mgr
    ->  where  a.ename = 'KING'
    -> union
    -> select  concat(a.ename,' - ',b.ename,' - ',
    ->                c.ename,' - ',d.ename)
    ->   from  emp a
    ->   inner join emp b
    ->      on a.empno = b.mgr
    ->   inner join emp c
    ->      on b.empno = c.mgr
    ->    left join emp d
    ->      on c.empno = d.mgr
    ->  where  a.ename = 'KING'
    ->     ) x
    ->  where emp_tree is not null
    ->  order by 1;
+------------------------------+
| emp_tree                     |
+------------------------------+
| KING                         |
| KING - BLAKE                 |
| KING - BLAKE - ALLEN         |
| KING - BLAKE - JAMES         |
| KING - BLAKE - MARTIN        |
| KING - BLAKE - TURNER        |
| KING - BLAKE - WARD          |
| KING - CLARK                 |
| KING - CLARK - MILLER        |
| KING - JONES                 |
| KING - JONES - FORD          |
| KING - JONES - FORD - SMITH  |
| KING - JONES - SCOTT         |
| KING - JONES - SCOTT - ADAMS |
+------------------------------+
14 rows in set (0.00 sec)

2.2 with遞歸方法

可以看到MySQL 8.0開始支持的with遞歸，可以讓代碼大大簡便股毫，代碼邏輯看起來也更有層次膳音。

with recursive emp2(ename,empno) AS
(
SELECT cast(ename as char(200)) ename,empno
  from emp
 where mgr is null
union ALL
SELECT concat(e2.ename,' - ',e1.ename) ,e1.empno
  from emp e1,emp2 e2
 where e1.mgr = e2.empno
)
select trim(ename) as ename
from emp2

測試記錄

mysql> with recursive emp2(ename,empno) AS
    -> (
    -> SELECT cast(ename as char(200)) ename,empno
    ->   from emp
    ->  where mgr is null
    -> union ALL
    -> SELECT concat(e2.ename,' - ',e1.ename) ,e1.empno
    ->   from emp e1,emp2 e2
    ->  where e1.mgr = e2.empno
    -> )
    -> select trim(ename) as ename
    -> from emp2;
+------------------------------+
| ename                        |
+------------------------------+
| KING                         |
| KING - JONES                 |
| KING - BLAKE                 |
| KING - CLARK                 |
| KING - JONES - SCOTT         |
| KING - JONES - FORD          |
| KING - BLAKE - ALLEN         |
| KING - BLAKE - WARD          |
| KING - BLAKE - MARTIN        |
| KING - BLAKE - TURNER        |
| KING - BLAKE - JAMES         |
| KING - CLARK - MILLER        |
| KING - JONES - SCOTT - ADAMS |
| KING - JONES - FORD - SMITH  |
+------------------------------+
14 rows in set (0.00 sec)