7-35 siblingsオプション

SQLパズル

親子テーブル
親  子
--  --
a1  h1
h1  z1
h1  z2
h1  z3
d1  x1
x1  f1
x1  f2
x1  f3

親子テーブルの階層情報を、子の昇順に出力する(ただし階層は崩さない)

出力結果
親  子  Level
--  --  -----
a1  h1  1
h1  z1  2
h1  z2  2
h1  z3  2
d1  x1  1
x1  f1  2
x1  f2  2
x1  f3  2

データ作成スクリプト

create table 親子(
親 char(2),
子 char(2));

insert into 親子 values('a1','h1');
insert into 親子 values('h1','z1');
insert into 親子 values('h1','z2');
insert into 親子 values('h1','z3');
insert into 親子 values('d1','x1');
insert into 親子 values('x1','f1');
insert into 親子 values('x1','f2');
insert into 親子 values('x1','f3');
commit;

SQL

select 親,子,Level
from 親子 a
start with not exists(select 1 from 親子 b
                       where b.子 = a.親)
connect by prior 子 = 親
order siblings by 子;

解説

siblingsオプションを指定すると階層を崩さずにソートできます。

マニュアルより引用
階層問合せでは、ORDER BYまたはGROUP BYを指定しないでください。
指定すると、CONNECT BYの結果の階層順序が壊れます。
同じ親の兄弟である行を順序付ける場合は、ORDER SIBLINGS BY句を使用します。

Oracleの階層問い合わせ2 (Level,sys_connect_by_path)

---