MySQL 创建表的分层视图

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

一.需求

返回一个结果集，它描述整个表的层次。
在EMP表中，员工KING没有经理，所以KING是根节点。
从KING开始，显示KING下面的所有员工以及KING下属的所有员工(如存在)。

最后，返回下列结果集:
±-----------------------------+
| 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和多个自联接

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)