PostgreSQL 递归查询简析

语法

WITH RECURSIVE cte_name AS(
    CTE_query_definition -- 非递归项
    UNION [ALL]
    CTE_query definion  -- 递归项
) SELECT * FROM cte_name;

递归 WITH 查询的一般形式始终是非递归项，然后是 UNION（或 UNION ALL），然后是递归项，其中只有递归项可以包含对查询自身输出的引用。

1. 非递归项：非递归项是一个 CTE 查询定义，它构成了 CTE 结构的基本结果集。
2. 递归项：递归项是使用 UNION 或 UNION ALL 运算符与非递归项连接的一个或多个 CTE 查询定义。递归术语引用 CTE 名称本身。
3. 终止检查：当前一次迭代没有返回任何行时，递归停止。

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
);

员工表包含三列：employee_id、manager_id 和 full_name。 manager_id 列指定员工的经理 ID。
​

• 插入数据

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(employee_id, full_name, manager_id) 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, subordinates s
        WHERE
          s.employee_id = e.manager_id
) SELECT * FROM subordinates;

结果

 employee_id | full_name |   manager_id
-------------+-----------+-----------------
           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)

这个过程是怎么工作的呢？

1. 先执行非递归项，获取基础结果集R0：

postgres=> SELECT employee_id, manager_id, full_name FROM employees WHERE employee_id = 2;

 employee_id | manager_id |  full_name
-------------+------------+-------------
           2 |          1 | Megan Berry
(1 row)

1. 把基础结果集 R0 作为临时表 subordinates 的查询结果，执行递归项，获取结果集R1：

postgres=> select employee_id, manager_id, full_name from employees e, subordinates s where s.employee_id = e.manager_id;

 employee_id |    full_name    | manager_id
-------------+-----------------+------------
           6 | Bella Tucker    |          2
           7 | Ryan Metcalfe   |          2
           8 | Max Mills       |          2
           9 | Benjamin Glover |          2
(4 rows)

1. 把结果集 R1 作为临时表 subordinates 的查询结果，执行递归项，获取结果集R2：

postgres=> select employee_id, manager_id, full_name from employees e, subordinates s where s.employee_id = e.manager_id;

 employee_id |    full_name    | manager_id
-------------+-----------------+------------
          16 | Piers Paige     |          7
          17 | Ryan Henderson  |          7
          18 | Frank Tucker    |          8
          19 | Nathan Ferguson |          8
          20 | Kevin Rampling  |          8
(5 rows)

1. 重复步骤 2-3，直到递归项的结果集 Rn 为空，退出循环，并把 R0, R1, ... Rn 的 UNION 或 UNION ALL 之后作为最终结果集返回。

注意：UNION 会丢弃重复的行和重复任何先前结果行的行，UNION ALL 保留所有行。

参考：https://www.postgresqltutorial.com/postgresql-recursive-query/