MySQL 创建表的分层视图

你的名字 2022-11-02 11:58 163阅读 0赞

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

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

# 一.需求 #

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

# 二.解决方案 #

## 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)

[scott_sql]: https://www.jianshu.com/p/532fe68924cb