PostgreSQL 对简单树的遍历，postgresql

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=# 

前序就是先根再左再右，后序就是先左再右再根，看书去理解就行，这名字肯定取得跟方法有关系，还是很好理解的。
 

#include "stdio.h"
#include"stdlib.h"
typedef struct btnode
{
int data;
struct btnode *lc,*rc;
}btnode,*bitree;
bitree precreate()
{
int ch;bitree root;
scanf("%d",&ch);
if(ch==0)
root=NULL;
else
{
root=(bitree)malloc(sizeof(btnode));
root->data=ch;
root->lc=precreate();
root->rc=precreate();
}
return root;
}
void preorder(bitree root)
{
if(root!=NULL)
{
preorder(root->lc);
preorder(root->rc);
printf("%d ",root->data);
}
}
void main()
{
bitree a;
a=precreate(a);
preorder(a);
}
帮你改好了，有问题联系！ 还有你那个遍历函数是先序遍历的，帮你改了，你试试。