postgresql递归查询使用CTE

最新推荐文章于 2025-11-03 09:57:16 发布

原创最新推荐文章于 2025-11-03 09:57:16 发布 · 1.4k 阅读

4 ·

CC 4.0 BY-SA版权

Hello,Bug

文章标签：

#postgresql

postgresql 专栏收录该内容

10 篇文章

订阅专栏

本文介绍PostgreSQL中CTE(Common Table Expressions)的使用方法，特别是递归查询的应用，通过实例展示了如何利用CTE进行层次结构的数据查询，并提供了一个递归查询的示例，用于展示年龄数据的连续性。

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