本篇内容介绍了“怎么用postgresql对树进行遍历”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!昨天我
本篇内容介绍了“怎么用postgresql对树进行遍历”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
昨天我用Mysql来实现了oracle的递归语句CONNECT BY, 看起来稍复杂些。今天来看看POSTGREsql如何实现ORACLE的CONNECT BY。
还是用昨天同样的表以及数据。POSTGRESQL自诩最像ORACLE的数据库,所以大部分语句也就都可以简单而且变相的实现了。
在这点上可以用他自己带的WITH递归功能,还可以用第三方扩展带来的类似connect by 函数。
先来看第一点,用递归的WITH来展现这棵树的路径。
t_girl=# with recursive tmp_country(id,path) as
t_girl-# (
t_girl(# select a.id,'/'||b.name as "path" from country_relation as a inner join country as b on (a.id = b.id) where a.parentid is null
t_girl(# uNIOn all
t_girl(# select a.id,q.path||'/'||b.name as "path" from country_relation as a inner join tmp_country as q on (q.id = a.parentid)
t_girl(# inner join country as b on (a.id = b.id)
t_girl(# )
t_girl-# select a.path from tmp_country as a;
path
-----------------------------------------------
/Earth
/Earth/North America
/Earth/South America
/Earth/Europe
/Earth/Asia
/Earth/Africa
/Earth/Australia
/Earth/North America/Canada
/Earth/North America/Central America
/Earth/North America/Island Nations
/Earth/North America/United States
/Earth/North America/United States/Alabama
/Earth/North America/United States/Alaska
/Earth/North America/United States/Arizona
/Earth/North America/United States/Arkansas
/Earth/North America/United States/California
(16 rows)
Time: 3.260 ms
还可以用tablefunc扩展带来的CONNECT BY函数把这棵树遍历出来。
由于昨天设计的两张表通过ID来关联,这个扩展自带的函数要把名字展现出来比较麻烦,索性这里我就用了一张临时表保存我想要的结果。
t_girl=# CREATE TEMPORARY TABLE tmp_country_relation as SELECT b.id,a.name,b.parentid,''::text as parentname FROM country AS a,country_relation AS b WHERE a.id = b.id;
SELECT 16
Time: 11.773 ms
t_girl=#
这里更新了对应的ID为NAME。
t_girl=# update tmp_country_relation set parentname = a.name from country as a where parentid = a.id;
UPDATE 15
Time: 1.829 ms
我用TABLEFUNC扩展带来的CONNECT BY 实现这棵树的遍历。
t_girl=# select path from connectby('tmp_country_relation as a','a.name','a.parentname','Earth',0,'/') as g(id text,parentid text,level int,path text) order by level;
path
----------------------------------------------
Earth
Earth/Australia
Earth/North America
Earth/Africa
Earth/South America
Earth/Europe
Earth/Asia
Earth/North America/Island Nations
Earth/North America/Canada
Earth/North America/Central America
Earth/North America/United States
Earth/North America/United States/California
Earth/North America/United States/Arkansas
Earth/North America/United States/Alabama
Earth/North America/United States/Alaska
Earth/North America/United States/Arizona
(16 rows)
Time: 5.974 ms
t_girl=#
“怎么用PostgreSQL对树进行遍历”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注编程网网站,小编将为大家输出更多高质量的实用文章!
--结束END--
本文标题: 怎么用PostgreSQL对树进行遍历
本文链接: https://www.lsjlt.com/news/57600.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-16
2024-05-16
2024-05-16
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0