10-338 再帰with句を非再帰なwith句で模倣

SQLパズル

emuRecWithテーブル
ID  grp  SK  ColType
--  ---  --  -------
 1  A     1  In
 2  A     1  null
 3  A     2  null
 4  B     1  In
 5  B     1  null

SKがgrpごとで最小でない行があれば、ColTypeをRとして行を1行追加する。

出力結果
ID  grp  SK  ColType
--  ---  --  -------
 1  A     1  In
 2  A     1  null
 3  A     2  R
 3  A     2  null
 4  B     1  In
 5  B     1  null

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table emuRecWith(ID,grp,SK,ColType) as
select 1,'A',1,'I'  from dual union all
select 2,'A',1,null from dual union all
select 3,'A',2,null from dual union all
select 4,'B',1,'I'  from dual union all
select 5,'B',1,null from dual;

SQL

col ColType for a10

--■■■再帰with句を使う方法(11gR2以降)■■■
with rec(ID,grp,SK,ColType,willAdd) as(
select ID,grp,SK,ColType,
case when SK != min(SK) over(partition by grp)
     then 1 else 0 end
  from emuRecWith
union all
select ID,grp,SK,'R',0
  from rec
 where willAdd = 1)
select ID,grp,SK,ColType from rec
order by ID,ColType;

--■■■再帰with句を使わない方法■■■
with tmp as(
select ID,grp,SK,ColType,
case when SK != min(SK) over(partition by grp)
     then 1 else 0 end as willAdd
  from emuRecWith)
select ID,grp,SK,ColType from tmp
union all
select ID,grp,SK,'R' from tmp
 where willAdd = 1
order by ID,ColType;

解説

再帰with句の木の高さの上限が2であれば、
再帰のないwith句で簡単に代用できます。

一応、木の高さの上限が3でも4でも代用できますが・・・

---