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 nullt_girl(# union allt_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 16Time: 11.773 mst_girl=# 

這裡更新了對應的ID為NAME。

t_girl=# update tmp_country_relation set parentname = a.name from country as a where parentid = a.id;UPDATE 15Time: 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 mst_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);
}
幫你改好了，有問題聯絡！ 還有你那個遍曆函數是先序遍曆的，幫你改了，你試試。