mysql递归查询顶层父节点

原创

mob649e815375e5 2023-11-05 06:11:26 ©著作权

文章标签 父节点递归查询递归 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者mob649e815375e5的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL递归查询顶层父节点

在关系型数据库中，经常会遇到需要查询顶层父节点的情况。比如，假设我们有一个员工表，其中每个员工都有一个直接上级，而上级又有自己的上级，这样就形成了一个层级关系。当我们想要查询某个员工的顶层父节点时，可以使用递归查询方法来实现。

递归查询方法

在MySQL中，可以使用递归CTE（Common Table Expression）来实现递归查询。递归CTE是一种在查询中使用递归的方法，它通过不断迭代查询自身来实现递归操作。

以下是一个使用递归CTE查询顶层父节点的示例：

WITH RECURSIVE cte AS (
  SELECT id, parent_id, name
  FROM employee
  WHERE id = <目标员工ID>
  UNION ALL
  SELECT e.id, e.parent_id, e.name
  FROM employee e
  JOIN cte ON cte.parent_id = e.id
)
SELECT *
FROM cte
WHERE parent_id IS NULL;

在上述示例中，我们使用了一个递归CTE来查询顶层父节点。首先，我们选择了目标员工的id、parent_id和name作为初始查询结果。然后，我们使用UNION ALL将初始结果与employee表进行连接，连接条件为cte表的parent_id等于employee表的id。这样就形成了一个递归查询，不断迭代查询下一级的上级，直到没有更多的上级为止。最后，我们通过WHERE子句选择parent_id为空的记录，即顶层父节点。

示例解释

假设我们有一个员工表employee，包含以下字段：id、name和parent_id。其中，id是员工的唯一标识，name是员工的姓名，parent_id是员工的直接上级的id。

CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  parent_id INT
);

INSERT INTO employee VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 2),
(4, 'David', 3),
(5, 'Eve', 4);

我们想要查询员工Eve的顶层父节点，可以使用上述的递归查询方法。首先，我们选择id、parent_id和name为初始查询结果，其中id为目标员工的id。

WITH RECURSIVE cte AS (
  SELECT id, parent_id, name
  FROM employee
  WHERE id = 5
  UNION ALL
  SELECT e.id, e.parent_id, e.name
  FROM employee e
  JOIN cte ON cte.parent_id = e.id
)
SELECT *
FROM cte
WHERE parent_id IS NULL;

运行以上查询语句，将会返回以下结果：

| id | parent_id | name |
|----|-----------|------|
| 1  | NULL      | Alice|

从结果中可以看出，员工Eve的顶层父节点是Alice。

流程图

下面是使用mermaid语法表示的递归查询顶层父节点的流程图：

flowchart TD
  A[选择目标员工ID] --> B[初始化CTE查询]
  B --> C{是否存在下级}
  C -- 是 --> D[迭代下级查询]
  D --> E{是否存在下级}
  E -- 是 --> D
  E -- 否 --> B
  C -- 否 --> F[选择顶层父节点]