階層問い合わせ

ストラクチャー型の部品表（構成マスタ）やツリー状のメニューなどで使えそうな、階層問い合わせについてまとめます。

階層問い合わせとは

次のようなツリー構造があるとします。

このツリーの関係をデータベースで表すと次のようなテーブルが用意できます。

このテーブルから「A」を頂点（根）として親子関係を紐解くには、次のSQL文を実行します。

SELECT parents,children 
FROM TREE_NODE
START WITH parents = 'A'
CONNECT BY PRIOR children = parents;

階層問い合わせでは、最初に START WITH 句で木の根となる条件が判定されます。

次に CONNECT BY 句で、ノード同士の親子条件が判定されます。 PRIOR 演算子は、親の行の値であることを示しており PRIOR children = parents で、(親の行の) children = (子の行の)parents という条件になります。

CONNECT BY 句の指定で、根（親）からの子、孫へと検索が行われ枝葉が展開されます。

parents      children
----------   -----------
A            B
B            E
B            F
A            C
C            E
C            G
A            D

Level 擬似列

結果として取得できていますが少しわかりにくいので、 Level 擬似列というものを使ってみます。Level 擬似列は、 START WITH 句の条件を満たしたノードのレベルを1として、子供をレベル2、孫をレベル3といった感じで深さを表現してくれます。

SELECT parents,children,Level 
FROM TREE_NODE
START WITH parents = 'A'
CONNECT BY PRIOR children = parents;

parents      children      Level
----------   -----------   -----------
A            B             1
B            E             2
B            F             2
A            C             1
C            E             2
C            G             2
A            D             1

子・孫から親へ逆展開

CONNECT BY 句を使用すると、枝葉から根へ遡ることも可能となります。

SELECT parents,children,Level 
FROM TREE_NODE
START WITH children = 'E'
CONNECT BY PRIOR parents = children;

parents      children      Level
----------   -----------   -----------
B            E             1
C            E             1
A            B             2
A            C             2