9-22 存在有無のブール値で論理演算

SQLパズル

CandidateSkillsテーブル
candidateID  skill_code
-----------  -------------
        100  accounting
        100  inventory
        100  manufacturing
        200  accounting
        200  inventory
        300  manufacturing
        400  inventory
        400  manufacturing
        500  accounting
        500  manufacturing

candidateIDごとに、
skill_code=manufacturing のレコードが存在し、
かつ
skill_code=inventory または skill_code=accounting のレコードが存在する
candidateIDの全レコードを出力する。

出力結果
candidateID  skill_code
-----------  -------------
        100  accounting
        100  inventory
        100  manufacturing
        400  inventory
        400  manufacturing
        500  accounting
        500  manufacturing

SQLパズル(日本語版)のパズル16 [職業斡旋] を参考にさせていただきました
SQLパズル 第2版のパズル17 [人材紹介会社] を参考にさせていただきました

データ作成スクリプト

create table CandidateSkills(
candidateID integer,
skill_code  text,
primary key(candidateid,skill_code));

insert into CandidateSkills values(100,'accounting');
insert into CandidateSkills values(100,'inventory');
insert into CandidateSkills values(100,'manufacturing');
insert into CandidateSkills values(200,'accounting');
insert into CandidateSkills values(200,'inventory');
insert into CandidateSkills values(300,'manufacturing');
insert into CandidateSkills values(400,'inventory');
insert into CandidateSkills values(400,'manufacturing');
insert into CandidateSkills values(500,'accounting');
insert into CandidateSkills values(500,'manufacturing');

SQL

--■■■window関数を使う方法■■■
select candidateID,skill_code
from (select candidateID,skill_code,
          bool_or(skill_code='manufacturing') over(partition by candidateID)
      and bool_or(skill_code in('inventory','accounting')) over(partition by candidateID)
       as willOut
        from CandidateSkills) a
 where willOut
order by candidateID,skill_code;

--■■■window関数を使わない方法1■■■
select candidateID,skill_code
  from CandidateSkills a
 where exists(select 1 from CandidateSkills b
               where b.candidateID = a.candidateID
              having bool_or(b.skill_code='manufacturing')
                 and bool_or(b.skill_code in('inventory','accounting')))
order by candidateID,skill_code;

--■■■window関数を使わない方法2■■■
select candidateID,skill_code
  from CandidateSkills a
 where exists(select 1 from CandidateSkills b
               where b.candidateID = a.candidateID
                 and b.skill_code in('manufacturing','inventory','accounting')
              having bool_or(b.skill_code='manufacturing')
                 and bool_or(b.skill_code in('inventory','accounting')))
order by candidateID,skill_code;

解説

PostgreSQLは、select文やテーブル値としても、boolean型をサポートします。
また、8.4からは、存在肯定命題の集合関数であるbool_or関数がwindow関数として使えます。

9.18. 集約関数

OracleSQLパズル 9-22 存在有無のブール値で論理演算

---