提要:在本教程中,您将学习使用递归公用数据表表达式或CTE的PostgreSQL递归查询。

简介

PostgreSQL 提供了 WITH 语句,允许您构建用于查询的辅助语句。这些辅助语句通常被称为公用数据表表达式(common table expressions)或CTE。CTE就像只存在于查询执行期间的临时表。

递归查询是引用递归CTE的查询。递归查询在许多情况下非常有用,例如查询分层数据:组织结构,物料清单等。

以下举例说明递归CTE的语法:

WITH cte_name(
    CTE_query_definition -- 非递归术语
    UNION [ALL]
    CTE_query definion  -- 递归术语
) SELECT * FROM cte_name;

递归CTE拥有三个元素:

  • 非递归术语(Non-recursive term):构成CTE结构的基本结果集
  • 递归术语(recursive term):使用 UNIONUNION ALL非递归术语 联合的一个或多个 CTE 查询。递归术语引用CTE名称本身。
  • 终止检查(termination check):当前一次迭代没有返回行时,递归停止。

PostgreSQL按以下顺序执行递归CTE:

  1. 执行非递归术语来创建基本结果集(R0)
  2. 以Ri作为输入执行递归项,以返回结果集Ri + 1作为输出
  3. 重复步骤2,直到返回空集。 (终止检查)
  4. 返回结果集R0,R1,... Rn的 UNION 或 UNION ALL 的最终结果集

示例

创建一个新表:

CREATE TABLE employees (
 employee_id serial PRIMARY KEY,
 full_name VARCHAR NOT NULL,
 manager_id INT
);

插入一些示例数据:

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的直接下属。这是员工表与下属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)