PostgresSQL8.4でツリー上に取得したい

質問

PostgresSQL8.4でツリー上に取得したい
こんにちわ。
PostgresSQLでツリー上にデータを取得したいのですが、思ったように取得できないのでご教授をお願いします画像の「取得したい順番」。
データはid,name,parentとの構造になっています。
データは親子構造になっており、親の下に子が並ぶようにしたいんです。
子は親のidをparentに持っています。
元のデータは画像の「元データ」になっています。
現状再起SQLでデータを取得しid or parentでソートを掛けているのですが、意図した通りに取得できません画像の「取得出来る順番」。
そもそも再起SQLでできるのか、相関サブクエリを使うのか・・・SQLのみでできるのかすら分からなくなってきました・・・。
何か画像の「取得したい順番」通りに取得する方法はないでしょうか。
ヒントだけでもいいので、ご教授をお願いします。

回答No.2

utakataXEX · ベストアンサー

#1 です。

余計なお世話かもしれませんが、親子関係で階層的にデータを持たせるのは、主たる要素の意味合いの粒度が同じ場合の方が好ましいです。

例えば、前便のサンプルで頻出する社員テーブルの場合、主キーは社員ID、エンティティの意味する粒度は親でも子でも変わらない「社員」です。

ご質問の場合、アジア→日本→東京（地域→国名→地名）のように粒度が荒いので、マスタとして今後、使いにくくなっていく可能性があります。

既に設計済みでもう変えられないなら、仕方ありませんが。

また、単純に地名マスタ程度の意味合いしかないのであれば、今のままでも問題無いと思います。

お礼

ご回答ありがとうございます。
質問であげさせて頂いているのは、質問用＆SQLの作成用で作ったデータです。
しかし実際のデータも粒度は荒いです・・・。こちらはすでに設計済みになっている点とある程度使用されてる為に変更することができません。
マスタについては少々懸念しています。
質問に答えて頂けるだけでなく、ご親切に懸念事項まで指摘して頂き大変ありがたく思います。
ありがとうございます。

回答No.3

pdragon

http://oraclesqlpuzzle.hp.infoseek.co.jp/postgresql-rec-with.html#1-5
こちらを参考にさせて頂きました。

---
with recursive rec(id, name, parent, path) as(
select id, name, parent, array[id]
from testtable
where parent = 0
union all
select b.id, b.name, b.parent, a.path || b.id
from rec a Join testtable b
on a.id = b.parent)
select id, name, parent, path, array_upper(path, 1) as LV
from rec order by path;
---

PostgreSQL9.0.1で試したところ、ご要望どおりの結果を取得できました。

参考URL：

http://oraclesqlpuzzle.hp.infoseek.co.jp/postgresql-rec-with.html#1-5

お礼

回答が遅くなり申し訳ございません。
試してみましたところ、無事にデータの取得ができました。
ありがとうございました。

回答No.1

utakataXEX

ORACLEだったら、CONNECT BYを使った階層問合せを使って楽勝にできる部分ですが、PostgreSQLでも階層問合せの機能を使用する事が可能です。
（標準では実装されません）

このような場合、connectby() を使います。

以下を参考にしてください。
↓
[ThinkIT] 第5回：SQL文の移行（2） (1/2)
http://thinkit.co.jp/free/marugoto/2/1/23/

PostgreSQLで階層問い合わせ（CONNECT BY）を使用する
http://ameblo.jp/b-nobu/entry-10029509981.html

PostgreSQLで階層問い合わせ（CONNECT BY）を使用する（その２）
http://ameblo.jp/b-nobu/entry-10030757897.html

PostgreSQL 階層問合せ

でググれば沢山サンプルが引っかかると思います。

参考URL：

http://thinkit.co.jp/free/marugoto/2/1/23/

お礼

ご回答ありがとうございます。
早速参考に試させて頂きます。