再帰with句05 階層問い合わせのconnect by nocycleを模倣

SQLパズル

noCycleT
ID  nextID
--  ------
 1       2
 2       3
 3       4
 4       1
 5       6

階層問い合わせを使った下記のクエリと同じ結果を取得する。

select ID,nextID,sys_connect_by_path(to_char(ID),',') as Path,
connect_by_IsCycle as IsCycle
  from noCycleT
start with ID = 1
connect by nocycle prior nextID = ID;

出力結果
ID  nextID  Path      IsCycle
--  ------  --------  -------
 1       2  ,1              0
 2       3  ,1,2            0
 3       4  ,1,2,3          0
 4       1  ,1,2,3,4        1

データ作成スクリプト

create table noCycleT(
ID     number primary key,
nextID number);

insert into noCycleT values(1,2);
insert into noCycleT values(2,3);
insert into noCycleT values(3,4);
insert into noCycleT values(4,1);
insert into noCycleT values(5,6);
commit;

SQL

col path for a10

--■■■connect_by_IsCycleは模倣しない場合■■■
with rec(ID,nextID,path) as(
select ID,nextID,',' || to_char(ID)
  from noCycleT
 where ID=1
union all
select b.ID,b.nextID,a.path || ',' || to_char(b.ID)
  from rec a,noCycleT b
 where a.nextID = b.ID)
CYCLE ID SET IsLoop TO 'Y' DEFAULT 'N'
select ID,nextID,path
  from rec
 where IsLoop = 'N';

--■■■connect_by_IsCycleも模倣する場合■■■
with rec(TreeID,ID,nextID,path) as(
select ID,ID,nextID,',' || to_char(ID)
  from noCycleT
 where ID=1
union all
select a.TreeID,b.ID,b.nextID,a.path || ',' || to_char(b.ID)
  from rec a,noCycleT b
 where a.nextID = b.ID)
CYCLE ID SET IsLoop TO 'Y' DEFAULT 'N'
select TreeID,ID,nextID,path,
case when exists(select 1 from rec b
                  where b.IsLoop = 'Y'
                    and a.TreeID = b.TreeID
                    and a.nextID = b.ID)
     then 1 else 0 end as IsCycle
  from rec a
 where IsLoop = 'N';

解説

同じ木で親子条件を満たして、かつ、訪問済なノードが存在すれば、
connect_by_IsCycleが1と判定してます。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
階層問い合わせのconnect by nocycleは、ループになる行を出力しませんが、
再帰with句のcycle句は、ループになる行を出力します。

with rec(ID,nextID,path) as(
select ID,nextID,',' || to_char(ID)
  from noCycleT
 where ID=1
union all
select b.ID,b.nextID,a.path || ',' || to_char(b.ID)
  from rec a,noCycleT b
 where a.nextID = b.ID)
CYCLE ID SET IsLoop TO 'Y' DEFAULT 'N'
select * from rec;

ID  nextID  path        IsLoop
--  ------  ----------  ------
 1       2  ,1          N
 2       3  ,1,2        N
 3       4  ,1,2,3      N
 4       1  ,1,2,3,4    N
 1       2  ,1,2,3,4,1  Y