pgsql递归查询

简介

PostgreSQL提供了WITH语句，允许你构造用于查询的辅助语句。这些语句通常称为公共表表达式或cte。cte类似于只在查询执行期间存在的临时表。递归查询是指递归CTE的查询。递归查询在很多情况下都很有用，比如查询组织结构、物料清单等层次数据

语法

WITH RECURSIVE cte_name(
    CTE_query_definition -- non-recursive term
    UNION [ALL]
    CTE_query definion  -- recursive term
) SELECT * FROM cte_name;

1.递归三要素

1.非递归项:非递归项是CTE查询定义，它构成CTE结构的基本结果集。

2.递归项:递归项是使用UNION或UNION ALL操作符将一个或多个CTE查询定义与非递归项连接起来。递归项引用CTE名称本身。

3.终止检查:当上一个迭代没有返回任何行时，递归将停止。

2.sql执行顺序

1.执行非递归项来创建基本结果集(R0)。

2.以Ri作为输入执行递归项，返回结果集Ri+1作为输出。

3.重复步骤2，直到返回一个空集。(终止检查)

4.返回最终的结果集，它是一个并集，或者是所有结果集R0、R1、……Rn的并集

例子

1.建表

// 员工表由三个列组成:employee_id、manager_id和全名。manager_id列指定employee的manager id。
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);

2.递归查询返回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;