階層データのあるテーブルで親から最下層の子までのパスを列挙するクエリ

階層データのあるテーブルで親から最下層の子までのパスを列挙するクエリ
すでに存在するシステムより親子関係のデータが自動的に作成されるのですが、
それをSQLクエリで並び替えることに苦労しています。

やりたいこと：
最上位の親から最下層の子までのパスを列挙するクエリを作成したい。

テーブル構造：
直属の親と子を１レコードで保存しています。
(ID=０は最上位の為、１が最上位の親)

TableName：Department
ID　ParentID　ChildID　Order　ChildName
--　--------　-------　-----　---------
0　　　　0　　　　1　　　1　　　xx
1　　　　0　　　　2　　　1　　　aa
2　　　　1　　　　3　　　1　　　bb
3　　　　1　　　　4　　　2　　　cc
4　　　　2　　　　5　　　1　　　dd
5　　　　2　　　　6　　　2　　　ee
6　　　　3　　　　7　　　1　　　ff
7　　　　3　　　　8　　　2　　　gg
8　　　　6　　　　9　　　1　　　hh
9　　　　6　　　　10　　　2　　　ii
10　　　　8　　　　11　　　1　　　jj

クエリで以下のように表示したい。

Level1　Level2　Level3　Level4　Level5　NodeName
------　------　------　------　------　--------
　　1　　　　　　　　　　　　　　　　　　　xx
　　1　　　　3　　　　　　　　　　　　　　bb
　　1　　　　4　　　　　　　　　　　　　　cc
　　1　　　　3　　　　7　　　　　　　　　　ff
　　1　　　　3　　　　8　　　　　　　　　　gg
　　1　　　　3　　　　11　　　　　　　　　jj
　　2　　　　　　　　　　　　　　　　　　aa
　　2　　　　5　　　　　　　　　　　　　　dd
　　2　　　　6　　　　　　　　　　　　　　ee
　　2　　　　6　　　　9　　　　　　　　　　hh
　　2　　　　6　　　　10　　　　　　　　　ii

私が試したSQLクエリは以下となります。
select lev3.Level1,lev3.Level2,Level3,Level4,Node5.ChildID as Level5,ChildName from Department as Node5
Right Join(select lev2.Level1,lev2.Level2,lev2.Level3,Node4.ChildID as Level4,ChildName from Department as Node4
Right Join(select level.Level1, level.Level2,Node3.ChildID as Level3,ChildName from Department as Node3
Right Join(select Node1.ChildID as Level1,Node2.ChildID as Level2,ChildName from Department as Node2
Right Join(select ChildID,NodeName.ChildName from Department as Node0
inner Join tbDepartment as NodeName on Node0.ChildID=NodeName.ID
Right Join(select ChildID as N0 from Department where ParentID=0) as Node00
on Node00.N0=Node0.ParentID) as Node1
on Node1.ChildID=Node2.ParentID) as level
on level.N2=Node3.ParentID) as lev2
on lev2.N3=Node4.ParentID) as lev3
on lev3.N4=Node5.ParentID

これだと、パスの列挙はできるのですが、名前が最上位の親だけしかわからず、
あたまが混乱しています。

有効な列挙方法を教えてください。

よろしくお願いします。

No.1 ベストアンサー 回答者： jamshid6 回答日時： 2010/04/22 02:47

再帰CTEの話をすると混乱するかもしれませんので、とりあえず以下を。

ただ、クエリを見る限り、Accessっぽいのが気になります。

SELECT
CASE
WHEN d5.ChildID IS NOT NULL THEN d5.ChildID
WHEN d4.ChildID IS NOT NULL THEN d4.ChildID
WHEN d3.ChildID IS NOT NULL THEN d3.ChildID
WHEN d2.ChildID IS NOT NULL THEN d2.ChildID
WHEN d1.ChildID IS NOT NULL THEN d1.ChildID END,
CASE
WHEN d5.ChildID IS NOT NULL THEN d4.ChildID
WHEN d4.ChildID IS NOT NULL THEN d3.ChildID
WHEN d3.ChildID IS NOT NULL THEN d2.ChildID
WHEN d2.ChildID IS NOT NULL THEN d1.ChildID END,
CASE
WHEN d5.ChildID IS NOT NULL THEN d3.ChildID
WHEN d4.ChildID IS NOT NULL THEN d2.ChildID
WHEN d3.ChildID IS NOT NULL THEN d1.ChildID END,
CASE
WHEN d5.ChildID IS NOT NULL THEN d2.ChildID
WHEN d4.ChildID IS NOT NULL THEN d1.ChildID END,
CASE
WHEN d5.ChildID IS NOT NULL THEN d1.ChildID END,
COALESCE(d1.ChildName,d2.ChildName,d3.ChildName,d4.ChildName,d5.ChildName)
FROM Department d1
LEFT OUTER JOIN Department d2 ON d2.ChildID=d1.ParentID
LEFT OUTER JOIN Department d3 ON d3.ChildID=d2.ParentID
LEFT OUTER JOIN Department d4 ON d4.ChildID=d3.ParentID
LEFT OUTER JOIN Department d5 ON d5.ChildID=d4.ParentID

この回答へのお礼

おお！いとも簡単に・・・
まだまだ勉強不足を痛感しています。こんな方法もあるんですね・・・
教えていただいて本当にありがとうございます。

ちなみに、Accessっぽいのは、もともとAccessから勉強しだした人間なので、
基礎が抜けきらない状態です。

SQLServer2000を手にしてSQL文を勉強しだしたので、初心者に近いです。・・・

再帰CTEなどの例文をいろいろやっていきま＝す。

お礼日時：2010/04/22 10:03