10-57 全てのIDを持つかチェック

SQLパズル

Boxテーブル
BoxID  BookID
-----  ------
    1     001
    1     002
    1     003
    1     004
    2     001
    2     003
    3     002
    3     002
    3     002
    4     001
    4     002
    4     003

BookIDが001、002、003のレコードを持つ
BoxIDを出力する

出力結果
BoxID
-----
    1
    4

データ作成スクリプト

create table Box(
BoxID  number(1),
BookID char(3));

insert into Box values(1,'001');
insert into Box values(1,'002');
insert into Box values(1,'003');
insert into Box values(1,'004');
insert into Box values(2,'001');
insert into Box values(2,'003');
insert into Box values(3,'002');
insert into Box values(3,'002');
insert into Box values(3,'002');
insert into Box values(4,'001');
insert into Box values(4,'002');
insert into Box values(4,'003');
commit;

SQL

--■■■分析関数を使う方法■■■
select distinct BoxID
from (select BoxID,
      count(distinct case when BookID in('001','002','003')
                     then BookID end)
      over(partition by BoxID) as BookCount
      from Box)
where BookCount = 3
order by BoxID;

--■■■集合演算を使う方法■■■
select distinct BoxID
  from Box a
 where not exists(select '001' from dual
                  union all
                  select '002' from dual
                  union all
                  select '003' from dual
                  minus
                  select b.BookID from Box b
                   where b.BoxID = a.BoxID)
order by BoxID;

--■■■存在肯定命題の論理積を求める方法■■■
select BoxID
  from Box
group by BoxID
having max(case BookID when '001' then 1 else 0 end)
     * max(case BookID when '002' then 1 else 0 end)
     * max(case BookID when '003' then 1 else 0 end) = 1
order by BoxID;

--■■■distinctオプションを指定したcount関数を使う方法■■■
select BoxID
  from Box
 where BookID in('001','002','003')
group by BoxID
having count(distinct BookID) = 3
order by BoxID;

解説

集合演算を使う方法では、
existsを使って、差集合が空集合かチェックしてます

---