1. 语法结构
with recursive 名字 as (
A.初始条件语句(非递归部分)
union [all]
B.递归部分语句
) [SELECT | INSERT | UPDATE | DELETE]
1.1 说明
- 前半部分A为初始条件语句，后半部分B为要进行的递归语句
- 先执行A语句，然后将A语句的结果作为B语句的条件，如果需要对查询结果去重则使用union进行连接，否则使用union all进行连接
2. 示例
2.1 表结构
DROP TABLE IF EXISTS "test"."rbac_menu";
CREATE TABLE "test"."rbac_menu" (
"id" int8 NOT NULL,
"pid" int8,
"menu_name" varchar(255) COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "test"."rbac_menu"."id" IS 'ID';
COMMENT ON COLUMN "test"."rbac_menu"."pid" IS '父ID';
COMMENT ON COLUMN "test"."rbac_menu"."menu_name" IS '菜单名称';
-- ----------------------------
-- Records of rbac_menu
-- ----------------------------
INSERT INTO "test"."rbac_menu" VALUES (100101, 1001, '权限管理');
INSERT INTO "test"."rbac_menu" VALUES (10010101, 100101, '菜单管理');
INSERT INTO "test"."rbac_menu" VALUES (10010102, 100101, '用户管理');
INSERT INTO "test"."rbac_menu" VALUES (10010103, 100101, '角色管理');
INSERT INTO "test"."rbac_menu" VALUES (1001010101, 10010101, '设置角色');
INSERT INTO "test"."rbac_menu" VALUES (1001010102, 10010101, '设置用户');
INSERT INTO "test"."rbac_menu" VALUES (1001010301, 10010103, '查看');
INSERT INTO "test"."rbac_menu" VALUES (1001010302, 10010103, '新增');
INSERT INTO "test"."rbac_menu" VALUES (1001010303, 10010103, '修改');
INSERT INTO "test"."rbac_menu" VALUES (1001010304, 10010103, '删除');
-- ----------------------------
-- Primary Key structure for table rbac_menu
-- ----------------------------
ALTER TABLE "test"."rbac_menu" ADD CONSTRAINT "rbac_menu_pkey" PRIMARY KEY ("id");
2.2 获取所有子节点信息
with recursive temp_table as (
-- 初始语句,仅执行一次
select "id", pid, "menu_name" from rbac_menu where pid = 1001
-- 使用union连接结果集(去重,不去重请使用"union all")
union
-- 递归语句
select a."id", a.pid, a."menu_name" from rbac_menu a,temp_table b where a.pid = b."id"
) select * from temp_table
order by pid, "id";
查询结果
2.3 获取所有子节点信息(控制递归层数)
with recursive temp_table as (
-- 初始语句,仅执行一次,设置一个变量 number=1
select
1 number, "id", pid, "menu_name"
from
rbac_menu
where
pid = 1001
-- 使用union连接结果集(去重,不去重请使用"union all")
union
-- 递归语句,执行number次,控制number,即可控制递归的层次
select
(number+1) as n, a."id", a.pid, a."menu_name"
from
rbac_menu a,temp_table b
where
a.pid = b."id"
and number < 2 -- 递归2层
) select * from temp_table
order by pid, "id";
2.4 获取所有父节点信息
with recursive temp_table as (
select "id", pid, "menu_name" from rbac_menu where "id" = 10060101
union
select a."id", a.pid, a."menu_name" from rbac_menu a,temp_table b where a.id = b.pid
) select * from temp_table
order by pid desc, "id" desc
查询结果
2.5 任意节点获取所有父节点及子节点信息(包括自身)
with recursive temp_table as (
select "id", pid, "menu_name" from rbac_menu where "id" = 10010103
union
select a."id", a.pid, a."menu_name" from rbac_menu a,temp_table b where a."id" = b.pid
), temp_table_b as (
select "id", pid, "menu_name" from rbac_menu where pid = 10010103
union
select a."id", a.pid, a."menu_name" from rbac_menu a,temp_table_b b where a.pid = b."id"
) select * from temp_table union select * from temp_table_b
order by pid, "id";
查询结果
