SQL 使用CTE遍历层次结构

在本文中，我们将介绍如何使用SQL中的公共表表达式（CTE）来遍历层次结构。

阅读更多：SQL 教程

什么是层次结构？

层次结构是一种组织数据的方式，其中每个元素都可以包含其他元素。例如，组织结构可以表示为层次结构，每个员工都可以有上级和下属。在数据库中，我们可以使用层次结构来表示这种关系。

使用CTE进行层次遍历

在SQL中，CTE是一种可以在查询中创建临时结果集的方法。我们可以使用CTE来遍历层次结构，无论层次结构有多少级别。

考虑一个表示组织结构的表，该表包含员工的ID、姓名和直属领导的ID。我们可以使用CTE来遍历整个组织结构并找到每个员工的所有上级。

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT ID, Name, SupervisorID
    FROM Employees
    WHERE ID = :employee_id
    UNION ALL
    SELECT e.ID, e.Name, e.SupervisorID
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ID = eh.SupervisorID
)
SELECT * FROM EmployeeHierarchy;

在上面的例子中，我们创建了一个名为EmployeeHierarchy的CTE，它包含了一个初始查询和递归查询。

初始查询选择指定员工的ID、姓名和直属领导的ID。递归查询使用INNER JOIN将查询结果与EmployeeHierarchy表连接，以获取每个员工的直属上级。这样，我们可以递归地遍历整个组织结构，直到没有更多的上级。

最后，我们选择CTE中的所有行来获取员工及其上级的完整层次结构。

示例

假设我们有以下Employees表的示例数据：

ID	Name	SupervisorID
1	John	NULL
2	Mike	1
3	Sarah	1
4	Jane	3
5	Mark	2

我们想要找到Sarah的所有上级。我们可以使用上面的查询来实现：

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT ID, Name, SupervisorID
    FROM Employees
    WHERE ID = 3
    UNION ALL
    SELECT e.ID, e.Name, e.SupervisorID
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ID = eh.SupervisorID
)
SELECT * FROM EmployeeHierarchy;