SQL 层级查询（一）
相信大家在工作中都遇到过存在层次关系的数据表，典型的例子诸如菜单表（多级菜单）、用户表（拥有上下级关系）、商品类目表（多级类目）。
我们经常用到的案例表 emp 就具有层次结构的数据，字段 empno 是员工编号，字段 mgr 则是员工的上级的编号。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
其中，mgr 为 NULL 表明该员工没有上级领导。
我们要把每个员工的所有上级领导都找出来，实现的效果如下：
empno ename path
------ ------ ----------------------
7369 SMITH ->FORD->JONES->KING
7499 ALLEN ->BLAKE->KING
7521 WARD ->BLAKE->KING
7566 JONES ->KING
7654 MARTIN ->BLAKE->KING
7698 BLAKE ->KING
7782 CLARK ->KING
7788 SCOTT ->JONES->KING
7839 KING
7844 TURNER ->BLAKE->KING
7876 ADAMS ->SCOTT->JONES->KING
7900 JAMES ->BLAKE->KING
7902 FORD ->JONES->KING
7934 MILLER ->CLARK->KING
对于编号为 7369 的 SMITH，他的直属领导的编号是 7902，姓名叫做 FORD；FORD 的直属领导叫做 JONES，编号为 7566；编号为 7566 的直属领导是编号为 7839 的 KING，而 KING 没有直属领导。因此，SMITH 的上级领导的关系链构成：->FORD->JONES->KING 。
对于找到每位员工的直属领导，使用一个自关联就能做到。
SELECT
a.empno,
a.ename,
b.ename AS leader
FROM
emp a
LEFT JOIN emp b
ON b.empno = a.mgr
由于 emp 表的层次关系的最大深度只有 4，因此，在原来查询父子关系的基础上再增加两个自关联就能把表中的所有关系都连接起来。
SELECT
a.empno,
a.ename,
CONCAT(
IFNULL(CONCAT('->', b.ename), ''),
IFNULL(CONCAT('->', c.ename), ''),
IFNULL(CONCAT('->', d.ename), '')
) AS path
FROM
emp a
LEFT JOIN emp b
ON b.empno = a.mgr
LEFT JOIN emp c
ON c.empno = b.mgr
LEFT JOIN emp d
ON d.empno = c.mgr
使用 IFNULL() 是为了避免当上级不存在时，ename 为 NULL 和所有字符串拼接的结果都为 NULL 。
如果层级太深，或者层次深度不确定，可以使用递归的方式解决。
WITH RECURSIVE leader_path(empno, ename, mgr, path) AS
(SELECT
empno,
ename,
mgr,
CAST('' AS CHAR(100)) AS path
FROM
emp
UNION ALL
SELECT
a.empno,
a.ename,
b.mgr,
CONCAT(
a.path,
IFNULL(CONCAT('->', b.ename), '')
)
FROM
leader_path a
LEFT JOIN emp b
ON a.mgr = b.empno
WHERE b.empno IS NOT NULL)
SELECT
empno,
ename,
path
FROM
leader_path
WHERE mgr IS NULL
ORDER BY 1
哈，使用递归的方案感觉要复杂了许多。使用递归需要注意几个地方：
- 在递归中一定要加入终止条件，本 SQL 的终止条件是
WHERE b.empno IS NOT NULL； - 遇到字符串拼接需要提前设置该字段的长度，对应到 SQL 中的操作是
CAST('' AS CHAR(100))； - 递归会生成中间结果，我们要把中间结果过滤掉，
WHERE mgr IS NULL就是只获取最终的结果。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2020-12-01，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读