再帰with句05 Oracleのconnect by nocycleを模倣

SQLパズル

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

Oracleの下記のクエリと同じ結果を取得する。

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     integer not null primary key,
nextID integer);

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

SQL

--■■■connect_by_iscycleは模倣しない場合■■■
with RowN(ID,nextID,rn) as(
select ID,nextID,
cast(digits(smallint(RowNumber() over(order by ID)))
as varchar(80))
  from noCycleT),
X(ID,nextID,path,rn,rnList) as(
select ID,nextID,
',' || cast(RTrim(char(ID)) as varchar(40)),
rn,',' || rn
  from RowN
 where ID =1
union all
select b.ID,b.nextID,
X.path || ',' || cast(RTrim(char(b.ID)) as varchar(40)),
b.rn,X.rnList || ',' || b.rn
  from X,RowN b
 where X.nextID = b.ID
   and LOCATE(b.rn,X.rnList,1) = 0)
select ID,nextID,path
  from X;

--■■■connect_by_iscycleも模倣する場合■■■
with RowN(ID,nextID,rn) as(
select ID,nextID,
cast(digits(smallint(RowNumber() over(order by ID)))
as varchar(80))
  from noCycleT),
X(TreeID,ID,nextID,path,rn,rnList) as(
select ID as TreeID,ID,nextID,
',' || cast(RTrim(char(ID)) as varchar(40)),
rn,',' || rn
  from RowN
 where ID =1
union all
select X.TreeID,b.ID,b.nextID,
X.path || ',' || cast(RTrim(char(b.ID)) as varchar(40)),
b.rn,X.rnList || ',' || b.rn
  from X,RowN b
 where X.nextID = b.ID
   and LOCATE(b.rn,X.rnList,1) = 0)
select ID,nextID,path,
case when exists(select 1 from X c
                  where X.TreeID  = c.TreeID
                    and X.nextID  = c.ID
                    and LOCATE(c.rn,X.rnList,1) != 0)
     then 1 else 0 end as IsCycle
  from X;

解説

同じ木で親子条件を満たすが、訪問済ノードを持つノードであれば、
connect_by_iscycleが1と判断してます。

Port CONNECT BY to DB2(英語)

---