トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
9-43 集合(重複要素を許可)の包含関係を調べる
SQLパズル
TableA TableB TableC TableD TableE
Val Val Val Val Val
--- --- --- --- ---
AAA AAA AAA AAA AAA
AAA AAA BBB AAA AAA
BBB BBB CCC BBB AAA
BBB BBB BBB BBB
BBB BBB BBB CCC
CCC CCC CCC CCC
CCC
TableAとTableB、
TableAとTableC、
TableAとTableD、
TableAとTableE
の包含関係を調べる。
各集合に重複した要素(重複行)は、
存在するものとする。
出力結果
TableB TableC TableD TableE
------ ------ ------ ------
A=B A⊃C A⊂D null
データ作成スクリプト
create table TableA(Val varchar2(3));
create table TableB(Val varchar2(3));
create table TableC(Val varchar2(3));
create table TableD(Val varchar2(3));
create table TableE(Val varchar2(3));
insert all
into tableA values('AAA')
into tableA values('AAA')
into tableA values('BBB')
into tableA values('BBB')
into tableA values('BBB')
into tableA values('CCC')
--
into TableB values('AAA')
into TableB values('AAA')
into TableB values('BBB')
into TableB values('BBB')
into TableB values('BBB')
into TableB values('CCC')
--
into TableC values('AAA')
into TableC values('BBB')
into TableC values('CCC')
--
into TableD values('AAA')
into TableD values('AAA')
into TableD values('BBB')
into TableD values('BBB')
into TableD values('BBB')
into TableD values('CCC')
into TableD values('CCC')
--
into TableE values('AAA')
into TableE values('AAA')
into TableE values('AAA')
into TableE values('BBB')
into TableE values('CCC')
into TableE values('CCC')
select 1 from dual;
commit;
SQL
--■■■分析関数を使う方法■■■
select
case when not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableB)
then 'A=B'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableA
minus
select Val,Row_Number() over(partition by Val order by 1) from TableB)
then 'A⊂B'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableB
minus
select Val,Row_Number() over(partition by Val order by 1) from TableA)
then 'A⊃B' end as TableB,
case when not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableC)
then 'A=C'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableA
minus
select Val,Row_Number() over(partition by Val order by 1) from TableC)
then 'A⊂C'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableC
minus
select Val,Row_Number() over(partition by Val order by 1) from TableA)
then 'A⊃C' end as TableC,
case when not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableD)
then 'A=D'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableA
minus
select Val,Row_Number() over(partition by Val order by 1) from TableD)
then 'A⊂D'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableD
minus
select Val,Row_Number() over(partition by Val order by 1) from TableA)
then 'A⊃D' end as TableD,
case when not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableE)
then 'A=E'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableA
minus
select Val,Row_Number() over(partition by Val order by 1) from TableE)
then 'A⊂E'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableE
minus
select Val,Row_Number() over(partition by Val order by 1) from TableA)
then 'A⊃E' end as TableE
from dual;
--■■■分析関数を使わない方法■■■
select
case when not exists(select Val,count(*) from TableA group by Val
minus
select Val,count(*) from TableB group by Val)
and not exists(select Val,count(*) from TableB group by Val
minus
select Val,count(*) from TableA group by Val)
then 'A=B'
when not exists(select 1 from TableA a group by Val
having count(*) > (select count(*) from TableB b
where b.Val = a.Val))
then 'A⊂B'
when not exists(select 1 from TableB b group by Val
having count(*) > (select count(*) from TableA a
where a.Val = b.Val))
then 'A⊃B' end as TableB,
case when not exists(select Val,count(*) from TableA group by Val
minus
select Val,count(*) from TableC group by Val)
and not exists(select Val,count(*) from TableC group by Val
minus
select Val,count(*) from TableA group by Val)
then 'A=C'
when not exists(select 1 from TableA a group by Val
having count(*) > (select count(*) from TableC b
where b.Val = a.Val))
then 'A⊂C'
when not exists(select 1 from TableC b group by Val
having count(*) > (select count(*) from TableA a
where a.Val = b.Val))
then 'A⊃C' end as TableC,
case when not exists(select Val,count(*) from TableA group by Val
minus
select Val,count(*) from TableD group by Val)
and not exists(select Val,count(*) from TableD group by Val
minus
select Val,count(*) from TableA group by Val)
then 'A=D'
when not exists(select 1 from TableA a group by Val
having count(*) > (select count(*) from TableD b
where b.Val = a.Val))
then 'A⊂D'
when not exists(select 1 from TableD b group by Val
having count(*) > (select count(*) from TableA a
where a.Val = b.Val))
then 'A⊃D' end as TableD,
case when not exists(select Val,count(*) from TableA group by Val
minus
select Val,count(*) from TableE group by Val)
and not exists(select Val,count(*) from TableE group by Val
minus
select Val,count(*) from TableA group by Val)
then 'A=E'
when not exists(select 1 from TableA a group by Val
having count(*) > (select count(*) from TableE b
where b.Val = a.Val))
then 'A⊂E'
when not exists(select 1 from TableE b group by Val
having count(*) > (select count(*) from TableA a
where a.Val = b.Val))
then 'A⊃E' end as TableD
from dual;
解説
not existsで差集合が空集合か調べてます。
select句で、count(*) over()
で要素数を求めて、minusで要素数を比較してます。
重複した要素を考慮するので、
minus allを模倣した集合演算を行ってます。
10-128 minus allとintersect all
一応、
分析関数を使わずに、包含関係を調べることもできます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
分析関数を使う方法には、例外があって、下記のwhen句では、
TableAが空集合(レコードがない)の場合に、必ず、'A=B'となってしまいます。
case when not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableB)
then 'A=B'
なので、TableAまたはTableBが空集合の場合を考慮すると
case when not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableB)
and not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableB
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA)
then 'A=B'
となります。
そして、空集合を考慮したSQLは、こうなります。
select
case when not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableB)
and not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableB
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA)
then 'A=B'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableA
minus
select Val,Row_Number() over(partition by Val order by 1) from TableB)
then 'A⊂B'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableB
minus
select Val,Row_Number() over(partition by Val order by 1) from TableA)
then 'A⊃B' end as TableB,
case when not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableC)
and not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableC
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA)
then 'A=C'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableA
minus
select Val,Row_Number() over(partition by Val order by 1) from TableC)
then 'A⊂C'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableC
minus
select Val,Row_Number() over(partition by Val order by 1) from TableA)
then 'A⊃C' end as TableC,
case when not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableD)
and not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableD
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA)
then 'A=D'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableA
minus
select Val,Row_Number() over(partition by Val order by 1) from TableD)
then 'A⊂D'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableD
minus
select Val,Row_Number() over(partition by Val order by 1) from TableA)
then 'A⊃D' end as TableD,
case when not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableE)
and not exists(select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableE
minus
select Val,Row_Number() over(partition by Val order by 1),
count(*) over() from TableA)
then 'A=E'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableA
minus
select Val,Row_Number() over(partition by Val order by 1) from TableE)
then 'A⊂E'
when not exists(select Val,Row_Number() over(partition by Val order by 1) from TableE
minus
select Val,Row_Number() over(partition by Val order by 1) from TableA)
then 'A⊃E' end as TableE
from dual;
CodeZine：SQLで集合演算
分析関数の衝撃4(完結編)
分析関数の衝撃5(総集編)