MySQL 确定哪些是叶节点、分子节点、根节点

小鱼儿 2022-11-03 04:02 133阅读 0赞

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

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

# 一.需求 #

确定给定行属于哪些类型的节点: 叶节点、分支节点及根节点。

对于这个例子，叶节点表示该员工不是经理；分支节点上的员工即是经理，又有经理根节点是没有经理的员工。  
通过返回1(TRUE)或0(FALSE),

# 二.解决方案 #

请注意，EMP表是树状层次模型，而不是递归层次模型，根节点的mgr值为null。  
如果emp是递归层次模型的话，根节点应有自引用(即员工KING的mgr值将是KING的empno)。  
实际上，自引用并不直观，故这里将根节点的mgr设为null值。  
对于递归层次模型中使用 connect by(Oracle）或with的情况，请务必当心:SQL可能会死循环，如果一定要采用递归层次，代码中必须考虑避免这样的循环。

select  e.ename,
            (select sign(count(*)) from emp d
              where 0 =
                (select count(*) from emp f
                  where f.mgr = e.empno)) as is_leaf,
            (select sign(count(*)) from emp d
              where d.mgr = e.empno
                and e.mgr is not null) as is_branch,
            (select sign(count(*)) from emp d
              where d.empno = e.empno
                and d.mgr is null) as is_root
      from  emp e
     order  by 4 desc,3 desc;

测试记录:

mysql> select  e.ename,
        ->         (select sign(count(*)) from emp d
        ->           where 0 =
        ->             (select count(*) from emp f
        ->               where f.mgr = e.empno)) as is_leaf,
        ->         (select sign(count(*)) from emp d
        ->           where d.mgr = e.empno
        ->             and e.mgr is not null) as is_branch,
        ->         (select sign(count(*)) from emp d
        ->           where d.empno = e.empno
        ->             and d.mgr is null) as is_root
        ->   from  emp e
        ->  order  by 4 desc,3 desc;
    +--------+---------+-----------+---------+
    | ename  | is_leaf | is_branch | is_root |
    +--------+---------+-----------+---------+
    | KING   |       0 |         0 |       1 |
    | JONES  |       0 |         1 |       0 |
    | BLAKE  |       0 |         1 |       0 |
    | CLARK  |       0 |         1 |       0 |
    | SCOTT  |       0 |         1 |       0 |
    | FORD   |       0 |         1 |       0 |
    | SMITH  |       1 |         0 |       0 |
    | ALLEN  |       1 |         0 |       0 |
    | WARD   |       1 |         0 |       0 |
    | MARTIN |       1 |         0 |       0 |
    | TURNER |       1 |         0 |       0 |
    | ADAMS  |       1 |         0 |       0 |
    | JAMES  |       1 |         0 |       0 |
    | MILLER |       1 |         0 |       0 |
    +--------+---------+-----------+---------+
    14 rows in set (0.00 sec)

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