5-12 共通集合を求める

SQLパズル

会員テーブル
会員ID  質問ID   解答
------  ------  -----
aaa      01      11111
aaa      02      22222
bbb      01      11111
bbb      02      33333
ccc      01      44444
ccc      02      22222

会員テーブルから
質問01の解答が11111、かつ、質問02の解答が22222の会員IDを出力する。

会員テーブルのプライマリキーは、会員IDと質問IDとする。

出力結果
会員ID
------
aaa

データ作成スクリプト

create table 会員(
会員ID char(3),
質問ID char(2),
解答   char(5),
primary key (会員ID,質問ID));

insert into 会員 values('aaa','01','11111');
insert into 会員 values('aaa','02','22222');
insert into 会員 values('bbb','01','11111');
insert into 会員 values('bbb','02','33333');
insert into 会員 values('ccc','01','44444');
insert into 会員 values('ccc','02','22222');
commit;

SQL

--■■■intersectを使用して、共通集合を求める方法■■■
select 会員ID from 会員
 where 質問ID='01'
   and 解答='11111'
intersect
select 会員ID from 会員
 where 質問ID='02'
   and 解答='22222';

--■■■サブクエリを使用する方法■■■
select 会員ID from 会員 a
 where 質問ID='01'
   and 解答='11111'
   and exists(select 1 from 会員 b
               where b.会員ID=a.会員ID
                 and b.質問ID='02'
                 and b.解答='22222');

--■■■分析関数を使用する方法■■■
select distinct 会員ID
from (select 会員ID,
      count(case when (質問ID,解答) in(('01','11111'),('02','22222'))
                 then 質問ID end) over(partition by 会員ID) as 件数
      from 会員)
where 件数 = 2;

--■■■having句でブール代数を使う方法■■■
select 会員ID
  from 会員
group by 会員ID
having max(case when 質問ID = '01' and 解答='11111'
                then 1 else 0 end)
     * max(case when 質問ID = '02' and 解答='22222'
                then 1 else 0 end) = 1;

解説

intersectを使用すると共通集合を取得できます。

---