PostgreSQL树形结构的递归查询示例

PostgreSQL通过WITH RECURSIVE语句实现树形结构的递归查询，允许在临时表中定义初始节点，递归扩展至所有子节点。

在数据库中处理树形结构数据是一项常见的任务，尤其是当我们使用关系型数据库如PostgreSQL时，这类数据通常表现为父子关系，例如组织结构、文件系统、网站导航菜单等，为了有效地查询这些树形结构的数据，我们可以使用递归查询，本文将介绍如何在PostgreSQL中使用递归公共表表达式（Recursive Common Table Expressions, Recursive CTEs）来执行这种查询。

PostgreSQL中的递归查询

PostgreSQL提供了对递归查询的支持，允许我们通过Common Table Expressions (CTEs)构建复杂的查询逻辑，递归CTE可以定义两部分：基本案例（base case）和递归案例（recursive case），基本案例是递归的起始点，而递归案例定义了如何从当前结果扩展到下一层。

示例：员工组织结构

考虑一个存储员工及其经理关系的简单表employees：

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(emp_id)
);

在这个表中，每个员工记录都有一个指向其直接经理的manager_id字段。

递归查询员工组织结构

假设我们需要查询某个员工（ID为1的员工）及其所有下属的树形结构，以下是如何使用递归CTE来实现这一点的示例：

WITH RECURSIVE subordinates AS (
    -基本案例： 选择初始员工
    SELECT emp_id, name, manager_id
    FROM employees
    WHERE emp_id = 1
    
    UNION ALL
    
    -递归案例： 选择下属员工
    SELECT e.emp_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.emp_id
)
-最终查询
SELECT * FROM subordinates;

解释

1、基本案例：我们从employees表中选取了特定员工（这里是ID为1的员工）。

2、递归案例：接着，我们通过UNION ALL操作符连接另一个查询，该查询查找所有将基本案例中的员工作为经理的员工，这是递归的部分，因为它引用了CTE自身（subordinates）。