SQL SQL 层次结构 – 解析给定节点的所有祖先的完整路径

在本文中，我们将介绍如何使用SQL查询来解析给定节点的所有祖先的完整路径。层次结构是指数据中各个元素之间的父子关系，常见的例子包括组织结构、文件系统和分类系统等。

阅读更多：SQL 教程

层次结构模型

在开始之前，我们需要了解一下层次结构模型。在层次结构中，每个节点都有一个父节点（除了根节点）和零个或多个子节点。节点之间的关系是树形结构，其中根节点是整个层次结构的起点。

例如，我们有一个名为“部门”的表，其中包含了各个部门的信息，以及每个部门的父部门的ID。该表的结构如下所示：

CREATE TABLE department (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  parent_id INT
);

现在，我们有一个ID为5的部门，我们想要查询其所有的祖先部门的完整路径。

使用递归查询祖先路径

为了查询给定节点的所有祖先节点的完整路径，我们可以使用递归查询。递归查询是指查询过程中调用自身的技术。

首先，我们需要创建一个递归CTE（通用表达式）来进行递归查询。CTE是一种在SQL中定义查询的方法，可以在查询中使用其自身的结果。

以下是一个使用递归CTE查询给定节点的所有祖先节点的完整路径的示例：

WITH RECURSIVE ancestor_path AS (
  SELECT id, name, parent_id, CAST(name AS varchar(1000)) AS path
  FROM department
  WHERE id = 5
  UNION ALL
  SELECT d.id, d.name, d.parent_id, CONCAT(ap.name, ' > ', d.name)
  FROM department d
  JOIN ancestor_path ap ON d.id = ap.parent_id
)
SELECT path
FROM ancestor_path;

在上面的示例中，我们首先从“department”表中选择给定ID为5的部门信息，并将其路径初始化为该部门的名字。

然后，我们通过联接“department”表和递归CTE“ancestor_path”来连接每个部门的父部门，将其添加到路径中。

最后，我们从递归CTE中选择最终的路径结果。

示例

让我们使用上述SQL查询语句来解析ID为5的部门的所有祖先部门的完整路径。

WITH RECURSIVE ancestor_path AS (
  SELECT id, name, parent_id, CAST(name AS varchar(1000)) AS path
  FROM department
  WHERE id = 5
  UNION ALL
  SELECT d.id, d.name, d.parent_id, CONCAT(ap.path, ' > ', d.name)
  FROM department d
  JOIN ancestor_path ap ON d.id = ap.parent_id
)
SELECT path
FROM ancestor_path;

查询的结果为：

path
-------------------
Marketing
Marketing > Sales
Marketing > Sales > Regional

由此可见，ID为5的部门的所有祖先部门的完整路径为：Marketing、Marketing > Sales和Marketing > Sales > Regional。

总结

本文介绍了如何使用递归查询来解析给定节点的所有祖先的完整路径。通过使用递归CTE，我们可以轻松地查询层次结构中节点的路径。这对于组织结构、文件系统和分类系统等涉及父子关系的数据非常有用。希望本文能帮助您理解并应用SQL中的层次结构查询技术。