备注:测试数据库版本为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)
