PostgreSQL遍曆簡單樹的方法教程

來源:互聯網
上載者:User

還是用上次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
-----------------------------------------------
 /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=#

 

 

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.