5-2 他レコードに対する存在チェック

SQLパズル

t1
ID  Kind  Disc
--  ----  ----
 1     A  aaaa
 1     B  bbbb
 2     A  xxxx
 3     A  yyyy
 3     B  zzzz
 4     B  ssss

テーブル ：t1
ID   (PK)
Kind (PK)
Disc
がある。
Kindはチェック制約により'A'、'B' の2種類しか格納できない。

Kind が'B'のレコードを出力したいが、
IDについて'A'しか存在しないレコードの場合は'A'を出力させたい。

IDについて'A'、'B'の両方の種類が存在するレコードも存在するし、
'A'のみ、'B'のみのレコードも存在する。

出力結果
ID  Kind  Disc
--  ----  ----
 1     B  bbbb
 2     A  xxxx
 3     B  zzzz
 4     B  ssss

データ作成スクリプト

create table t1(
ID   number(1),
Kind char(1) check (Kind in('A','B')),
Disc char(4),
primary key (ID,Kind));

insert into t1(ID,Kind,Disc) values(1,'A','aaaa');
insert into t1(ID,Kind,Disc) values(1,'B','bbbb');
insert into t1(ID,Kind,Disc) values(2,'A','xxxx');
insert into t1(ID,Kind,Disc) values(3,'A','yyyy');
insert into t1(ID,Kind,Disc) values(3,'B','zzzz');
insert into t1(ID,Kind,Disc) values(4,'B','ssss');
commit;

SQL

--■■■相関サブクエリを使う方法■■■
select ID,Kind,Disc from t1 a
where Kind = 'B'
or (Kind = 'A' and not exists(select 1 from t1 b
                               where b.ID=a.ID
                                 and b.Kind='B'))
order by ID;

--■■■分析関数を使う方法■■■
select ID,Kind,Disc
from (select ID,Kind,Disc,
      max(Disc) over(partition by ID) as MaxDisc
      from t1)
where Disc = MaxDisc
order by ID;

--■■■Keepを使う方法■■■
select ID,max(Kind) as Kind,
max(Disc) Keep(Dense_Rank Last order by Kind) as Disc
  from t1
group by ID
order by ID;

解説

相関サブクエリを使う方法では、
KindがAの場合は、
IDが同じで、KindがBのレコードの存在しないことをexistsでチェックしてます。