PostgreSQL递归查询示例

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/

PostgreSQL递归查询示例

标签：临时表   sql   postgre   values   min   dom   str   init   插入