10-128 minus allとintersect all

SQLパズル

T1    T2
 X     Y
--    --
 1     1
 1     1
 1     3
 1
 2

PostgreSQLで使える
minus all(except all)とintersect all
と同じ結果を取得するSQLをOracleで作成する。

出力結果1 (select x from t1 minus all select y from t2)
 X
--
 1
 1
 2

出力結果2 (select x from t1 intersect all select y from t2)
 X
--
 1
 1

データ作成スクリプト

create table t1 as
select 1 as x from dual
union all select 1 from dual
union all select 1 from dual
union all select 1 from dual
union all select 2 from dual;

create table t2 as
select 1 as y from dual
union all select 1 from dual
union all select 3 from dual;

SQL

--■■■Row_Number関数と集合演算を使う方法■■■
select x
  from (select x,Row_Number() over(partition by x order by 1) from t1
  minus select y,Row_Number() over(partition by y order by 1) from t2);

select x
    from (select x,Row_Number() over(partition by x order by 1) from t1
intersect select y,Row_Number() over(partition by y order by 1) from t2);

--■■■Row_Number関数と相関サブクエリを使う方法■■■
select x
  from (select x,Row_Number() over(partition by x order by 1) as Rank
          from t1) a
 where Rank > (select count(*) from t2 b where b.y=a.x);

select x
  from (select x,Row_Number() over(partition by x order by 1) as Rank
          from t1) a
 where Rank <= (select count(*) from t2 b where b.y=a.x);

解説

Row_Number関数でナンバリングしてから、集合演算を使う方法や、
Row_Number関数でナンバリングしてから、相関サブクエリで件数を見る方法があります。

分析関数の衝撃5 (総集編)

---