文章目标：整理PostgreSQL 递归查询的方式，方便自己和大家快速查看。 PostgreSQL 递归查询（含层级和结构） ✅ 创作者：Jay… 个人主页：Jay的个人主页 展望：若本篇讲解内容帮助到您，请帮忙点个赞吧，您的支持是我继续写作的最大动力，谢谢。
背景
父子关系的表中，避免不了相关正向查询，和反向查询的业务逻辑。
根据已知的“父对象”，递归查询所有的子级对象。根据已知的“子对象”，递归查询所有的父级对象。根据已知的“子对象/父对象”，递归查询所有的父级对象/子对象，并带有层级和结构。
对于第三点业务需求，PostgreSQL默认没有类似于Oracle的关键字“Level”，如果需要获取层级，需要自行构建，或者通过安装cross这个脚本来进行扩展（cross脚本处于安装目录，具体位置自行百度）。
实践
准备数据
创建表
create table city(id varchar(3) , pid varchar(3) , name varchar(10));
插入数据
insert into city values('002' , 0 , '浙江省');
insert into city values('001' , 0 , '广东省');
insert into city values('003' , '002' , '衢州市');
insert into city values('004' , '002' , '杭州市') ;
insert into city values('005' , '002' , '湖州市');
insert into city values('006' , '002' , '嘉兴市') ;
insert into city values('007' , '002' , '宁波市');
insert into city values('008' , '002' , '绍兴市') ;
insert into city values('009' , '002' , '台州市');
insert into city values('010' , '002' , '温州市') ;
insert into city values('011' , '002' , '丽水市');
insert into city values('012' , '002' , '金华市') ;
insert into city values('013' , '002' , '舟山市');
insert into city values('014' , '004' , '上城区') ;
insert into city values('015' , '004' , '下城区');
insert into city values('016' , '004' , '拱墅区') ;
insert into city values('017' , '004' , '余杭区') ;
insert into city values('018' , '011' , '金东区') ;
insert into city values('019' , '001' , '广州市') ;
insert into city values('020' , '001' , '深圳市') ;
insert into city values('021' , '013' , '普陀区');
业务实践
根据已知的“父对象” - > “浙江省”，递归查询所有的子级对象。
SQL:
WITH RECURSIVE cte AS
(
SELECT id, name, pid FROM city WHERE id = '002'
UNION ALL
SELECT child.id, child.name, child.pid FROM city child INNER JOIN cte parent ON parent.id = child.pid
)
SELECT * FROM cte
结果：
根据已知的“子对象” - “普陀区”，递归查询所有的父级对象。
SQL：
WITH RECURSIVE cte AS
(
SELECT id, name, pid FROM city WHERE id = '021'
UNION ALL
SELECT parent.id, parent.name, parent.pid FROM city parent INNER JOIN cte child ON parent.id = child.pid
)
SELECT * FROM cte
结果：
根据已知的“子对象” - “普陀区”，递归查询所有的父级对象，并带有层级和结构。
SQL:
WITH RECURSIVE cte AS
(
SELECT id, name, pid, name::varchar(150) as combined_name, id::varchar(150) AS combined_id, 1 AS LEVEL
FROM city WHERE id = '021'
UNION ALL
SELECT parent.id, parent.name, parent.pid, (child.combined_name || '>' || parent.name)::varchar(150) AS combined_name,
(child.combined_id || '>' || parent.id)::varchar(150) AS combined_id, child.LEVEL + 1 AS LEVEL
FROM city parent INNER JOIN cte child ON parent.id = child.pid
)
SELECT * FROM cte
结果：
跨层级反查时，可以考虑利用组合的id或者名称作为入口。
FAQ
1、ERROR: recursive query “t” column 2 has type character varying(150) in non-recursive term but type character varying overall
针对拼接的字段，需要重新定义其数据类型及长度。参考如下：
精彩内容
发表评论