再帰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;

解説

根からの経路でソートしてます。

Port CONNECT BY to DB2(英語)

---