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

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

如需要scott用户下建表及录入数据语句，可参考:
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)