トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
再帰with句04 Oracleのconnect_by_IsLeafを模倣
SQLパズル
IsLeafT
ID OyaID
-- -----
1 null
2 1
3 2
4 3
5 1
6 5
7 2
20 null
21 20
22 21
Oracleの下記のクエリと同じ結果を取得する。
select ID,OyaID,Level,sys_connect_by_path(to_char(ID),',') as Path,
connect_by_IsLeaf as IsLeaf
from IsLeafT
start with OyaID is null
connect by prior ID = OyaID;
出力結果
ID OyaID Level Path IsLeaf
-- ----- ----- --------- ------
1 null 1 ,1 0
2 1 2 ,1,2 0
3 2 3 ,1,2,3 0
4 3 4 ,1,2,3,4 1
7 2 3 ,1,2,7 1
5 1 2 ,1,5 0
6 5 3 ,1,5,6 1
20 null 1 ,20 0
21 20 2 ,20,21 0
22 21 3 ,20,21,22 1
データ作成スクリプト
create table IsLeafT(
ID integer not null primary key,
OyaID integer);
insert into IsLeafT values
( 1,null),
( 2, 1),
( 3, 2),
( 4, 3),
( 5, 1),
( 6, 5),
( 7, 2),
(20,null),
(21, 20),
(22, 21);
commit;
SQL
--■■■exists述語を使う方法■■■
with X(ID,OyaID,Level,path) as(
select ID,OyaID,1,',' || cast(RTrim(char(ID)) as varchar(40))
from IsLeafT
where OyaID is null
union all
select b.ID,b.OyaID,Level+1,path || ',' || cast(RTrim(char(b.ID)) as varchar(40))
from X,IsLeafT b
where X.ID = b.OyaID)
select ID,OyaID,Level,path,
case when exists(select 1 from X c
where X.ID = c.OyaID)
then 0 else 1 end as IsLeaf
from X;
--■■■深さ優先探索の行きがけ順でレベルを比較する方法■■■
with RowN(ID,OyaID,rn) as(
select ID,OyaID,
cast(digits(smallint(RowNumber() over(order by ID)))
as varchar(80))
from IsLeafT),
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,
case when Level < Lead(Level) over(order by skey)
then 0 else 1 end as isLeaf
from X;
解説