トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
再帰with句03 Oracleのorder siblings byを模倣
SQLパズル
siblingsT
ID OyaID
-- -----
1 null
2 1
3 1
4 1
5 3
6 3
7 4
8 4
9 6
10 7
20 null
21 20
22 20
23 21
24 21
Oracleの下記のクエリと同じ結果を取得する。
select connect_by_root ID as treeID,
ID,OyaID,Level,
sys_connect_by_path(to_char(ID),',') as path
from siblingsT
start with OyaID is null
connect by prior ID = OyaID
order siblings by ID desc;
出力結果
treeID ID OyaID Level path
------ -- ----- ----- ---------
20 20 null 1 ,20
20 22 20 2 ,20,22
20 21 20 2 ,20,21
20 24 21 3 ,20,21,24
20 23 21 3 ,20,21,23
1 1 null 1 ,1
1 4 1 2 ,1,4
1 8 4 3 ,1,4,8
1 7 4 3 ,1,4,7
1 10 7 4 ,1,4,7,10
1 3 1 2 ,1,3
1 6 3 3 ,1,3,6
1 9 6 4 ,1,3,6,9
1 5 3 3 ,1,3,5
1 2 1 2 ,1,2
データ作成スクリプト
create table siblingsT(
ID integer not null primary key,
OyaID integer);
insert into siblingsT values
( 1,null),
( 2, 1),
( 3, 1),
( 4, 1),
( 5, 3),
( 6, 3),
( 7, 4),
( 8, 4),
( 9, 6),
(10, 7),
(20,null),
(21, 20),
(22, 20),
(23, 21),
(24, 21);
commit;
SQL
with RowN(ID,OyaID,rn) as(
select ID,OyaID,
cast(digits(smallint(RowNumber() over(order by ID desc)))
as varchar(80))
from siblingsT),
X(treeID,ID,OyaID,Level,Path,rn,skey) as(
select ID as treeID,ID,OyaID,1,
',' || cast(RTrim(char(ID)) as varchar(40)),
rn,',' || rn
from RowN
where OyaID is null
union all
select X.treeID,b.ID,b.OyaID,Level+1,
X.path || ',' || cast(RTrim(char(b.ID)) as varchar(40)),
b.rn,X.skey || ',' || b.rn
from X,RowN b
where X.ID = b.OyaID)
select treeID,ID,OyaID,Level,path
from X
order by skey;
解説