10-137 特殊なdistinct

SQLパズル

DataTable
C1  C2
--  --
 1   2
 1   3
 1   7
 2   2
 2   4
 3   5
 8   4
 8   6
 9   7

以下の仕様(ソートは、order by Col1,Col2)で
出力します

1  2 --Will be selected
1  3 --wont be selected coz, 1 is alrready selected in col1
2  2 --wont be selected coz, 2 is alrready selected in col2
2  4 --will be selected
3  5 --will be selected
8  4 --wont be selected coz, 4 is alrready selected in col2
8  6 --will be selected
9  7 --will be selected

表関数等を使ってもいいとします

出力結果
C1  C2
--  --
 1   2
 2   4
 3   5
 8   6
 9   7

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

データ作成スクリプト

create table DataTable as
select 1 as c1,2 as c2 from dual
union all select 1,3 from dual
union all select 1,7 from dual
union all select 2,2 from dual
union all select 2,4 from dual
union all select 3,5 from dual
union all select 8,4 from dual
union all select 8,6 from dual
union all select 9,7 from dual;

create global temporary table WillOut(
C1 number(1),
C2 number(1))
on commit delete rows;

SQL

begin
    for rec in (select c1,c2
                  from DataTable
                order by c1,c2) Loop
        insert into WillOut(C1,C2)
        select rec.c1,rec.c2 from dual
        where not exists(select 1 from WillOut b
                          where rec.c1 = b.C1
                             or rec.c2 = b.C2);
    end Loop;
end;
/

select C1,C2
  from WillOut
order by C1,C2;

解説

SQLだけでやるのは、
再帰的な処理があるので難しそうですね

---