PostgreSQL 递归查询
在PostgreSQL中，递归查询是一种强大的工具，用于处理层次结构或树状数据。递归查询允许你在一个查询中反复引用自身，从而遍历或处理具有递归关系的数据。本文将详细介绍递归查询的概念、语法和实际应用。
什么是递归查询？
递归查询是一种特殊的SQL查询，它通过反复引用自身来处理具有递归关系的数据。常见的应用场景包括组织结构、文件系统、评论线程等层次结构数据。
在PostgreSQL中，递归查询通过WITH RECURSIVE关键字实现。WITH RECURSIVE允许你定义一个递归公共表表达式（CTE），并在查询中反复引用它。
递归查询的基本语法
递归查询的基本语法如下：
sql
WITH RECURSIVE cte_name AS (
-- 非递归部分（初始查询）
initial_query
UNION ALL
-- 递归部分
recursive_query
)
-- 最终查询
SELECT * FROM cte_name;
- cte_name：递归CTE的名称。
- initial_query：初始查询，用于生成递归的起点。
- recursive_query：递归查询，引用
cte_name自身，用于生成下一层数据。 - UNION ALL：将初始查询和递归查询的结果合并。
递归查询的工作原理
递归查询的执行过程可以分为以下几个步骤：
- 初始查询：执行
initial_query，生成递归的起点。 - 递归查询：执行
recursive_query，引用cte_name自身，生成下一层数据。 - 合并结果：将初始查询和递归查询的结果合并。
- 重复执行：重复执行递归查询，直到没有新的数据生成。
实际案例：组织结构查询
假设我们有一个员工表employees，其中包含员工的ID、姓名和上级ID。我们希望查询某个员工的所有下属（包括间接下属）。
表结构
sql
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
manager_id INT REFERENCES employees(id)
);
示例数据
sql
INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 2),
(4, 'David', 2),
(5, 'Eve', 1);
递归查询示例
sql
WITH RECURSIVE subordinates AS (
-- 初始查询：找到直接下属
SELECT id, name, manager_id
FROM employees
WHERE manager_id = 1
UNION ALL
-- 递归查询：找到间接下属
SELECT e.id, e.name, e.manager_id
FROM employees e
INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;
查询结果
| id | name | manager_id |
|---|---|---|
| 2 | Bob | 1 |
| 5 | Eve | 1 |
| 3 | Charlie | 2 |
| 4 | David | 2 |
备注
在这个例子中，我们首先找到manager_id = 1的直接下属（Bob和Eve），然后通过递归查询找到他们的下属（Charlie和David）。
递归查询的终止条件
递归查询必须有一个终止条件，否则会导致无限循环。在PostgreSQL中，递归查询的终止条件是递归查询不再生成新的数据。
警告
如果递归查询没有正确设置终止条件，可能会导致查询无限循环，消耗大量资源。
实际案例：文件系统路径
假设我们有一个文件系统表files，其中包含文件的ID、名称和父目录ID。我们希望查询某个目录下的所有文件路径。
表结构
sql
CREATE TABLE files (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
parent_id INT REFERENCES files(id)
);
示例数据
sql
INSERT INTO files (id, name, parent_id) VALUES
(1, 'root', NULL),
(2, 'home', 1),
(3, 'user', 2),
(4, 'documents', 3),
(5, 'photos', 3);
递归查询示例
sql
WITH RECURSIVE file_paths AS (
-- 初始查询：找到根目录
SELECT id, name, parent_id, name::TEXT AS path
FROM files
WHERE parent_id IS NULL
UNION ALL
-- 递归查询：构建文件路径
SELECT f.id, f.name, f.parent_id, fp.path || '/' || f.name
FROM files f
INNER JOIN file_paths fp ON f.parent_id = fp.id
)
SELECT * FROM file_paths;
查询结果
| id | name | parent_id | path |
|---|---|---|---|
| 1 | root | NULL | root |
| 2 | home | 1 | root/home |
| 3 | user | 2 | root/home/user |
| 4 | documents | 3 | root/home/user/documents |
| 5 | photos | 3 | root/home/user/photos |
提示
在这个例子中，我们通过递归查询构建了每个文件的完整路径。
总结
递归查询是PostgreSQL中处理层次结构数据的强大工具。通过WITH RECURSIVE，你可以轻松地遍历树状结构、构建路径或解决其他递归问题。本文介绍了递归查询的基本语法、工作原理和实际应用场景，并提供了详细的代码示例。
附加资源
练习
- 修改组织结构查询，使其返回每个员工的层级（例如，Alice为1级，Bob为2级，Charlie为3级）。
- 尝试在文件系统路径查询中添加一个条件，只返回路径中包含
photos的文件。
注意
在编写递归查询时，请务必确保有正确的终止条件，以避免无限循环。