PostgreSQL 提供了 WITH 语句, 允许你构造用于查询的辅助语句. 这些语句通常称为公共表表达式或 cte.cte 类似于只在查询执行期间存在的临时表.
递归查询是指递归 CTE 的查询. 递归查询在很多情况下都很有用, 比如查询组织结构, 物料清单等层次数据
下面演示了递归 CTE 的语法:
- WITH RECURSIVE cte_name(
- CTE_query_definition -- non-recursive term
- UNION [ALL]
- CTE_query definion -- recursive term
- ) SELECT * FROM cte_name;
递归 CTE 有三个元素:
1. 非递归项: 非递归项是 CTE 查询定义, 它构成 CTE 结构的基本结果集.
2. 递归项: 递归项是使用 UNION 或 UNION ALL 操作符将一个或多个 CTE 查询定义与非递归项连接起来. 递归项引用 CTE 名称本身.
3. 终止检查: 当上一个迭代没有返回任何行时, 递归将停止.
PostgreSQL 按以下顺序执行递归 CTE:
1. 执行非递归项来创建基本结果集 (R0).
2. 以 Ri 作为输入执行递归项, 返回结果集 Ri+1 作为输出.
3. 重复步骤 2, 直到返回一个空集.(终止检查)
4. 返回最终的结果集, 它是一个并集, 或者是所有结果集 R0,R1,......Rn 的并集
我们将创建一个新表来演示 PostgreSQL 递归查询.
- CREATE TABLE employees (
- employee_id serial PRIMARY KEY,
- full_name VARCHAR NOT NULL,
- manager_id INT
- );
员工表由三个列组成: employee_id,manager_id 和全名. manager_id 列指定 employee 的 manager 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;
上面 sql 的工作原理:
1. 递归 CTE subordinates 定义了一个非递归项和一个递归项.
2. 非递归项返回基本结果集 R0, 即 id 为 2 的员工.
- employee_id | manager_id | full_name
- -------------+------------+-------------
- 2 | 1 | Megan Berry
递归项返回员工 id 2 的直接下属. 这是 employee 表和 subordinates 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 递归查询.
参考地址: http://www.postgresqltutorial.com/postgresql-recursive-query/
来源: http://www.bubuko.com/infodetail-3091963.html