再帰with句02 Oracleのsys_connect_by_path関数を模倣

SQLパズル

IDTable
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
  from IDTable
start with OyaID is null
connect by prior ID = OyaID;

出力結果
ID  OyaID  Level  Path
--  -----  -----  ---------
 1   null      1  ,1
 2      1      2  ,1,2
 3      2      3  ,1,2,3
 4      3      4  ,1,2,3,4
 7      2      3  ,1,2,7
 5      1      2  ,1,5
 6      5      3  ,1,5,6
20   null      1  ,20
21     20      2  ,20,21
22     21      3  ,20,21,22

データ作成スクリプト

create table IDTable(
ID    integer not null primary key,
OyaID integer);

insert into IDTable values
( 1,null),
( 2,   1),
( 3,   2),
( 4,   3),
( 5,   1),
( 6,   5),
( 7,   2),
(20,null),
(21,  20),
(22,  21);

SQL

with recursive X(ID,OyaID,ArrPath) as(
select ID,OyaID,array[ID]
  from IDTable
 where OyaID is null
union all
select b.ID,b.OyaID,X.ArrPath || b.ID
  from X,IDTable b
 where X.ID = b.OyaID)
select ID,OyaID,array_upper(ArrPath,1) as Level,
',' || array_to_string(ArrPath,',') as path
  from X
order by ArrPath;

解説

配列型の使いどころでしょうねぇ
8.14. 配列

---