PostgreSQL遍历简单树的方法教程
发布时间：2022-06-29 00:41:06 所属栏目：MySql教程 来源：互联网
导读：上次我们讲了 MySQL存储过程实现Oracle邻接模型树形处理的方法实例,现在我们来看看POSTGRESQL如何实现ORACLE的CONNECT BY,我们可以用PostgreSQL的WITH递归遍历树. 还是用上次MySQL存储过程实现Oracle邻接模型树形处理的方法实例同样的表以及数据,POSTGRESQ
|
上次我们讲了 MySQL存储过程实现Oracle邻接模型树形处理的方法实例,现在我们来看看POSTGRESQL如何实现ORACLE的CONNECT BY,我们可以用PostgreSQL的WITH递归遍历树. 还是用上次MySQL存储过程实现Oracle邻接模型树形处理的方法实例同样的表以及数据,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 --phpfensi.com ----------------------------------------------- /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=#。 （编辑：应用网_丽江站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330468号