Hierarchical Queryの簡単な例

Hierarchical Query(階層的クエリ)は、親と子など階層的データを持ったテーブルからデータを取り出す際に使用すると便利なクエリです。

（裏技的な用途として、何もないところから10000行並んだテーブルを取得したりできます。）

簡単な例を以下に記しました。

Hierarchical Queryの簡単な例

-- 今回のクエリで使うテーブルを定義します
WITH CONTAINS_HIERARCHICAL_DATA AS(
SELECT 1 id, null parent, 'ルート' name from dual
UNION ALL	SELECT 2 id, 1 parent, '子' name from dual
UNION ALL	SELECT 3 id, 2 parent, '孫' name from dual
UNION ALL	SELECT 4 id, 4 parent, '孫' name from dual
UNION ALL	SELECT 5 id, 2 parent, 'ひ孫' name from dual
UNION ALL	SELECT 6 id, 5 parent, 'ひひ孫' name from dual
UNION ALL	SELECT 7 id, 1 parent, '子' name from dual
UNION ALL	SELECT 8 id, null parent, 'ルート' name from dual
)

SELECT
-- LEVELというのはHierarchical Query上で使える擬似列です。
	LEVEL "階層の深さ",
	LPAD(' ', (LEVEL - 1) * 3, ' ') || ID || ':' || NAME "構造の図"
FROM
	CONTAINS_HIERARCHICAL_DATA

-- こっからHierarchical Queryのメイン部分
-- ルートとなる行の条件を指定します。
START WITH PARENT IS NULL AND ID = 1
-- PRIORは数少ない単項演算子です。
CONNECT BY PRIOR ID = PARENT -- (2008/10/26 -> 本来は親にPRIORつけるべき)

-- 兄弟（同じ階層にある行）の並び替えの順番指定です。
ORDER SIBLINGS BY ID DESC

クエリの結果

|階層の深さ|構造の図|
|1|1:ルート|
|2|　　　7:子|
|2|　　　2:子|
|3|　　　　　　5:ひ孫|
|4|　　　　　　　　　6:ひひ孫|
|3|　　　　　　3:孫|