九月 5, 2023
摘要：在本教程中，您将了解使用递归公共表表达式或 CTE 的 PostgreSQL 递归查询。
PostgreSQL 递归查询简介
PostgreSQL 提供的WITH语句允许您构造用于查询的辅助语句。
这些语句通常称为公共表表达式或 CTE。CTE 就像临时表，仅在查询执行期间存在。
递归查询是指引用了递归 CTE 的查询。递归查询在许多情况下都很有用，例如查询组织结构、物料清单等分层数据。
下面说明了递归 CTE 的语法：
WITH RECURSIVE cte_name AS(
CTE_query_definition -- non-recursive term
UNION [ALL]
CTE_query definion -- recursive term
) SELECT * FROM cte_name;
递归 CTE 具有三个要素：
- 非递归项：非递归项是形成 CTE 结构的基本结果集的 CTE 查询定义。
- 递归项：递归项是使用 UNION 或
UNION ALL运算符与非递归项连接的一个或多个 CTE 查询定义。递归项引用 CTE 名称本身。 - 终止检查：当上一次迭代没有返回行时，递归停止。
PostgreSQL 按以下顺序执行递归 CTE：
- 执行非递归项以创建基本结果集 (R0)。
- 以 Ri 作为输入执行递归项，返回结果集 Ri+1 作为输出。
- 重复步骤 2，直到返回空集。（终止检查）
- 返回最终结果集，即结果集 R0、R1、… Rn 的 UNION 或
UNION ALL。
PostgreSQL 递归查询示例
我们将创建一个新表来演示 PostgreSQL 递归查询。
CREATE TABLE employees (
employee_id serial PRIMARY KEY,
full_name VARCHAR NOT NULL,
manager_id INT
);
该employees表包含三列：employee_id、manager_id和full_name。manager_id列指定员工的经理 ID。
以下语句将示例数据插入到employees表中。
INSERT INTO employees (
employee_id,
full_name,
manager_id
)
VALUES
(1, 'Michael North', NULL),
(2, 'Megan Berry', 1),
(3, 'Sarah Berry', 1),
(4, 'Zoe Black', 1),
(5, 'Tim James', 1),
(6, 'Bella Tucker', 2),
(7, 'Ryan Metcalfe', 2),
(8, 'Max Mills', 2),
(9, 'Benjamin Glover', 2),
(10, 'Carolyn Henderson', 3),
(11, 'Nicola Kelly', 3),
(12, 'Alexandra Climo', 3),
(13, 'Dominic King', 3),
(14, 'Leonard Gray', 4),
(15, 'Eric Rampling', 4),
(16, 'Piers Paige', 7),
(17, 'Ryan Henderson', 7),
(18, 'Frank Tucker', 8),
(19, 'Nathan Ferguson', 8),
(20, 'Kevin Rampling', 8);
以下查询返回 ID 为 2 的经理的所有下属。
WITH RECURSIVE subordinates AS (
SELECT
employee_id,
manager_id,
full_name
FROM
employees
WHERE
employee_id = 2
UNION
SELECT
e.employee_id,
e.manager_id,
e.full_name
FROM
employees e
INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
*
FROM
subordinates;
怎么运行的：
- 递归 CTE（下属）定义了一个非递归项和一个递归项。
- 非递归项返回基本结果集 R0，即 ID 为 2 的员工。
employee_id | manager_id | full_name
-------------+------------+-------------
2 | 1 | Megan Berry
递归项返回 ID 为 2 的员工的直接下属。这是employees表和下属 CTE 之间连接的结果。递归项的第一次迭代返回以下结果集：
employee_id | manager_id | full_name
-------------+------------+-----------------
6 | 2 | Bella Tucker
7 | 2 | Ryan Metcalfe
8 | 2 | Max Mills
9 | 2 | Benjamin Glover
PostgreSQL 重复执行递归项。递归成员的第二次迭代使用上面步骤的结果集作为输入值，并返回此结果集：
employee_id | manager_id | full_name
-------------+------------+-----------------
16 | 7 | Piers Paige
17 | 7 | Ryan Henderson
18 | 8 | Frank Tucker
19 | 8 | Nathan Ferguson
20 | 8 | Kevin Rampling
第三次迭代返回空结果集，因为没有员工向 ID 为 16、17、18、19 和 20 的员工汇报。
PostgreSQL 返回的最终结果集是非递归项和递归项生成的第一次和第二次迭代中所有结果集的并集。
employee_id | manager_id | full_name
-------------+------------+-----------------
2 | 1 | Megan Berry
6 | 2 | Bella Tucker
7 | 2 | Ryan Metcalfe
8 | 2 | Max Mills
9 | 2 | Benjamin Glover
16 | 7 | Piers Paige
17 | 7 | Ryan Henderson
18 | 8 | Frank Tucker
19 | 8 | Nathan Ferguson
20 | 8 | Kevin Rampling
(10 rows)
在本教程中，您学习了如何使用递归 CTE 构建 PostgreSQL 递归查询。