postgresql递归查询使用CTE

关注

原创最新推荐文章于 2026-10-31 14:23:51 发布 1.5k 阅读 AI 写同款· GEO 优化 ›

PostgreSQL与DuckDB递归CTE查询性能对比与优化递归CTE（Common Table Expression）是SQL中处理层次化数据的核心语法，通过WITH RECURSIVE实现树形结构遍历和图数据分析。其执行原理本质是迭代计算：先处理基础查询（anchor member），再循环处理递归部分直到满足终止条件。在OLTP场景中，成熟的查询优化器能动态调整JOIN策略和内存分配，而OLAP引擎则更依赖列式存储和并行计算优势。当处理深度递归查询时，不同数据库的实现差异会导致显著性能差距，例如PostgreSQL的智能Hash Join优化对比DuckDB的阅读详情

with查询是PostgreSQL支持的高级SQL特性之一，这一特性常称为CTE（Common Table Expressions），with查询就相当于在查询中定义的临时表，应用场景层次结构或树状结构
但是递归查询有一个重要属性，RECURSIVE,使用RECURSIVE属性可以引用自己的输出，从而实现递归。

至于怎么用接下来看我的案例吧！

存在一张包含以下数据的表
表数据列表

当给定一个id时能得到它完整的地名，id=7时，地名是:中国辽宁沈阳和平区，当id=5时，地名是:中国辽宁大连

思路步骤
1.使用PostgreSQL的WITH查询ID为7以及以上的所有父节点和名字
2.将输出的记过的name字段合并一个,通过string_agg函数实现(函数传送门)

具体代码如下

with recursive t as(
	select * from test_area where id = 7
	union all
	select test_area.* from test_area,t where test_area.id=t.fatherid
) 
select string_agg(n.name,'') from (select name,id from t  order by id) n

总结CTE的好处
1.CTE可以简化SQL代码，减少SQL嵌套层数，提高SQL代码的可读性。
2.CTE的辅助语句只需要计算一次，在主查询中可以多次使用。
3.当不需要共享查询结果时，相比视图更轻量。

=2021.08.10分割线===========
其中，我想要一个sql我想要一个SQL查询将第二行数据显示为第一行，将第三行数据显示为第二行，依此类推
举个例子，我想要年龄为这样

年龄(岁)	下一个年龄(岁)
1岁	2岁

2岁	3岁

3岁	4岁

实现的sql语句用递归查询

WITH CTE AS
(
	
	select 

	toUInt64OrZero(age) as age
	from resident_long rl
	group by age
	order by age
)
SELECT base.age `年龄(岁)`, nxt.age `下一个年龄(岁)`
from CTE base
LEFT OUTER JOIN CTE nxt on nxt.age = base.age + 1