PostgreSQL-With子句实现递归

哈库拉玛塔塔

通用表达式加强——递归

我们可以在 WITH 语句的基础上增加 RECURSIVE 修饰符来实现递归。

这一语法用在 查询部门层级、人物关系、资源树 时特别好用

准备

基础使用语法我们就不赘述了，直接用 查询部门层级 例子来说明：

首先我们准备一张 部门表

CREATE TABLE "public"."department" (
  "id" int4 NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "parent_id" int4,
  CONSTRAINT "department_pkey" PRIMARY KEY ("id")
);

插入准备的数据

INSERT INTO "public"."department"("id", "name", "parent_id") VALUES (1, '顶级部门', 0);
INSERT INTO "public"."department"("id", "name", "parent_id") VALUES (2, '一级部门1', 1);
INSERT INTO "public"."department"("id", "name", "parent_id") VALUES (3, '一级部门2', 1);
INSERT INTO "public"."department"("id", "name", "parent_id") VALUES (4, '一级部门3', 1);
INSERT INTO "public"."department"("id", "name", "parent_id") VALUES (5, '二级部门1', 2);
INSERT INTO "public"."department"("id", "name", "parent_id") VALUES (6, '二级部门2', 2);
INSERT INTO "public"."department"("id", "name", "parent_id") VALUES (7, '二级部门3', 2);
INSERT INTO "public"."department"("id", "name", "parent_id") VALUES (8, '二级部门4', 3);
INSERT INTO "public"."department"("id", "name", "parent_id") VALUES (9, '二级部门5', 4);
INSERT INTO "public"."department"("id", "name", "parent_id") VALUES (10, '三级部门1', 5);

查看导入数据是否成功

SELECT * FROM "department"；

到这一步结束，我们创建一个很简易的 部门表 ，它共有三个字段：

id 为本部门的唯一主键

name 为本部门的名称

parent_id 为本部门的父级部门

表结构很简单明了， 一个部门会有一个父部门也可能还会有多个子部门

上手

-- 自顶向下
with recursive tree AS (
    select dep.id,dep.name,dep.parent_id from department dep where dep.id = 1
    union all 
    select dep.id,dep.name,dep.parent_id from department dep inner join tree on dep.parent_id = tree.id
) select * from tree;
-- 查询的结果
-- id   name    parent_id
-- 1    顶级部门    0
-- 2    一级部门1   1
-- 3    一级部门2   1
-- 4    一级部门3   1
-- 5    二级部门1   2
-- 6    二级部门2   2
-- 7    二级部门3   2
-- 8    二级部门4   3
-- 9    二级部门5   4
-- 10   三级部门1   5

该语句是从顶级部门（ id = 1）向下查询，将所有顶级部门的子部门全部查询出来。可以尝试修改第二行最后 dep.id = 1 中数值 "1" ，取值范围为其他部门 id` 即可。

-- 自下向上
with recursive tree as (
    select dep.id,dep.name,dep.parent_id from department dep where dep.id =7
    union all 
    select dep.id,dep.name,dep.parent_id from department dep inner join tree on tree.parent_id = dep.id
) select * from tree;
-- 查询的结果
-- id   name    parent_id
-- 7    二级部门3   2
-- 2    一级部门1   1
-- 1    顶级部门    0

该语句是从子部门（ id = 7）向上查询，即查询该部门的父部门。同样的我们也可以修改第二行后数值 ”7“ 来查看不同的结果。

语法

with recursive 名字 as (
    A.初始语句（非递归部分）
    union all 
    B.递归部分语句
) [SELECT | INSERT | UPDATE | DELETE]

可以到 WITH RECURSIVE 与普通的 WITH 相比，最大的不同就是 as () 中的的内容。内容被 union all 一分为二，前半部分 A 为非递归语句，后半部分 B 为要进行的递归语句。

执行步骤如下

执行 A 部分。（如果使用的是union而非union all，则需对结果去重）其结果作为 B 中对result的引用，同时将这部分结果放入临时的working table中
重复执行如下步骤，直到working table为空：用working table的内容替换递归的自引用，执行 B ，（如果使用union而非union all，去除重复数据），并用该结果（如果使用union而非union all，则是去重后的结果）替换working table

注意项

初始语句 A 中的列与递归部分语句 B 的列必须要一一对应
初始语句 A 、递归部门语句 B 查询的时候不要使用 * 号来代替列
一定要有结束条件，否则会进入死循环

思考

在我们准备数据的时候， 顶级部门 的 parent_id 为 0，尝试改为它自身的 id 1 呢，然后执行语句：

-- 自下向上
with recursive tree as (