PostgreSQL遍历简单树的方法教程
发布:smiling 来源: PHP粉丝网 添加日期:2015-04-20 15:06:38 浏览: 评论:0
上次我们讲了 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=#
Tags: PostgreSQL mysql遍历单树
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)