欢迎您访问 最编程 本站为您分享编程语言代码，编程技术文章！

热门搜索/Hot Search

您现在的位置是： 首页

深入理解 SQL 分层查询（第二部分）

最编程 2024-07-26 15:25:58
...

在上一篇文章里，我们介绍了在 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

上一篇： 剖析jQuery中的层级选择器操作方法

下一篇： 论文编号结构层次详解

推荐阅读