在上一篇文章里,我们介绍了在 MySQL 中实现层次查询的两种方式。前文举的示例是获取从叶子点到根节点的路径,今天我们要实现的是从根节点找到所有叶子节点。
依旧以 emp 表为例,遍历所有员工数据,计算每个员工所在的层级(假设根节点所在层级为 1,mgr 为 NULL
的员工所在的节点为根节点 )。
emp 表的部分数据如下:
empno ename mgr
------ ------ --------
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING (NULL)
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
比如,KING 的节点为根节点,层级为 1,它有三个子节点,分别对应的编号是:7566、7698、7782,它们的层级为 2;其中,编号为 7566 的 JONES 有两个子节点:7788 和 7902,它们对应的层级为 3。
即使我们知道 emp 表中的员工的关系最深只有 4 级,使用多个自关联依然没法直接计算出各个员工的层级。因此,我们暂且用递归的方式实现。
锁定根节点:
SELECT
empno,
ename,
mgr,
1 AS lv
FROM
emp
WHERE mgr IS NULL
找到根节点下的子节点:
SELECT
b.empno,
b.ename,
b.mgr,
lv + 1
FROM
(SELECT
empno,
ename,
mgr,
1 AS lv
FROM
emp
WHERE mgr IS NULL) a
INNER JOIN emp b
ON a.empno = b.mgr;
empno ename mgr lv + 1
------ ------ ------ --------
7566 JONES 7839 2
7698 BLAKE 7839 2
7782 CLARK 7839 2
再把父子节点的关系套入递归表达式模板,最终的 SQL :
WITH RECURSIVE leader_path (empno, ename, mgr, lv) AS
(SELECT
empno,
ename,
mgr,
1 AS lv
FROM
emp WHERE mgr IS NULL
UNION ALL
SELECT
b.empno,
b.ename,
b.mgr,
lv + 1
FROM
leader_path a
INNER JOIN emp b
ON a.empno = b.mgr )
SELECT
empno,
ename,
lv
FROM
leader_path
ORDER BY 1
所有员工的层级 >>>
empno ename lv
------ ------ --------
7369 SMITH 4
7499 ALLEN 3
7521 WARD 3
7566 JONES 2
7654 MARTIN 3
7698 BLAKE 2
7782 CLARK 2
7788 SCOTT 3
7839 KING 1
7844 TURNER 3
7876 ADAMS 4
7900 JAMES 3
7902 FORD 3
7934 MILLER 3