10-100 同一グループの値の種類を取得

SQLパズル

ColTable
Col1  Col2  Col3  Col4
----  ----  ----  ----
aaaa     1     1     1
aaaa     2     1     1
aaaa     3     1     1
bbbb     1     1     1  ←出力対象
bbbb     2     1     2  ←出力対象
cccc     1     1     1  ←出力対象
cccc     2     2     1  ←出力対象
dddd     1     1     1  ←出力対象
dddd     2     2     2  ←出力対象
eeee     1     1     1  ←出力対象
eeee     2     2     2  ←出力対象
eeee     3     3     3  ←出力対象
ffff     1     1     1

同じCol1で、
Col3、Col4、がひとつでも違うものがある、
Col1、Col2、Col3、Col4を出力する。

データ作成スクリプト

create Table ColTable(
Col1 char(4),
Col2 number(1),
Col3 number(1),
Col4 number(1));

insert into ColTable values('aaaa',1,1,1);
insert into ColTable values('aaaa',2,1,1);
insert into ColTable values('aaaa',3,1,1);
insert into ColTable values('bbbb',1,1,1);
insert into ColTable values('bbbb',2,1,2);
insert into ColTable values('cccc',1,1,1);
insert into ColTable values('cccc',2,2,1);
insert into ColTable values('dddd',1,1,1);
insert into ColTable values('dddd',2,2,2);
insert into ColTable values('eeee',1,1,1);
insert into ColTable values('eeee',2,2,2);
insert into ColTable values('eeee',3,3,3);
insert into ColTable values('ffff',1,1,1);
commit;

SQL

--■■■分析関数を使う方法■■■
select Col1,Col2,Col3,Col4
from (select Col1,Col2,Col3,Col4,
      count(distinct Col3) over(partition by Col1) as Col3Count,
      count(distinct Col4) over(partition by Col1) as Col4Count
      from ColTable)
where 2 <= any(Col3Count,Col4Count);

--■■■相関サブクエリを使う方法■■■
select Col1,Col2,Col3,Col4
  from ColTable a
 where exists(select 1 from ColTable b
               where b.Col1 = a.Col1
                 and (b.Col3 != a.Col3
                   or b.Col4 != a.Col4));

解説

分析関数を使う方法では、
Col3の種類と、Col4の種類の
少なくとも一つが、2以上かチェックしてます。

all述語を、全てが、と解釈して、
any述語を、少なくとも一つ、と解釈すると、
数学と似たような表現となります。

---