SQL 层级查询（二）
在上一篇文章里，我们介绍了在 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
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2020-12-03，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。