Oracleの階層問い合わせ（3）
（connect_by_IsLeaf,connect_by_root）

connect_by_IsLeaf,connect_by_rootを使ったOracleの階層問い合わせ3

2. connect_by_root演算子

　connect_by_root演算子は、木の根である行の値を取得するのに使います。サンプルを見てみましょう。

IsRootT

ID	OyaID
1	null
2	1
3	1
4	1
5	3
6	3
7	4
8	4
9	6
10	null
20	null
21	20
22	20
23	21
24	21

　木の根である行のID列を、列別名rootIDとして取得してみます。

木の根の値を取得

select ID,OyaID,Level,connect_by_root ID as rootID,
sys_connect_by_path(to_char(ID),',') as path
  from IsRootT
start with OyaID is null
connect by prior ID = OyaID;

出力結果

ID	OyaID	Level	rootID	path
1	null	1	1	,1
2	1	2	1	,1,2
3	1	2	1	,1,3
5	3	3	1	,1,3,5
6	3	3	1	,1,3,6
9	6	4	1	,1,3,6,9
4	1	2	1	,1,4
7	4	3	1	,1,4,7
8	4	3	1	,1,4,8
10	null	1	10	,10
20	null	1	20	,20
21	20	2	20	,20,21
23	21	3	20	,20,21,23
24	21	3	20	,20,21,24
22	20	2	20	,20,22

　connect_by_root演算子のイメージは、下記となります。木ごとに区切る赤線をイメージして、根であるノードに茶色を塗ってます。

connect_by_root演算子のイメージ

　もうひとつのサンプルとして、木ごとに、幅優先探索順に出力してみます。

幅優先探索順に出力

select ID,OyaID,Level,sys_connect_by_path(to_char(ID),',') as path
  from IsRootT
start with OyaID is null
connect by prior ID = OyaID
order by connect_by_root ID,Level,path;

出力結果

ID	OyaID	Level	path
1	null	1	,1
2	1	2	,1,2
3	1	2	,1,3
4	1	2	,1,4
5	3	3	,1,3,5
6	3	3	,1,3,6
7	4	3	,1,4,7
8	4	3	,1,4,8
9	6	4	,1,3,6,9
10	null	1	,10
20	null	1	,20
21	20	2	,20,21
22	20	2	,20,22
23	21	3	,20,21,23
24	21	3	,20,21,24