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 number(3),
skill_code  varchar2(14),
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');
commit;

SQL

--■■■分析関数を使う方法■■■
select candidateID,skill_code
from (select candidateID,skill_code,
      max(decode(skill_code,'manufacturing',1,0)) over(partition by candidateID) as manufacturing,
      max(decode(skill_code,'inventory',1,0)) over(partition by candidateID) as inventory,
      max(decode(skill_code,'accounting',1,0)) over(partition by candidateID) as accounting
        from CandidateSkills)
where manufacturing*(inventory+accounting) >= 1
order by candidateid,skill_code;

--■■■分析関数を使わない方法1■■■
select candidateID,skill_code
  from CandidateSkills
 where candidateID in(select b.candidateID
                        from CandidateSkills b
                       group by b.candidateID
                      having max(decode(b.skill_code,'manufacturing',1,0)) *
                            (max(decode(b.skill_code,'inventory',1,0)) +
                             max(decode(b.skill_code,'accounting',1,0))) >= 1)
order by candidateID,skill_code;

--■■■分析関数を使わない方法2■■■
select candidateID,skill_code
  from CandidateSkills a
 where exists(select 1 from CandidateSkills b
               where b.candidateid = a.candidateid
              having max(decode(b.skill_code,'manufacturing',1,0)) *
                    (max(decode(b.skill_code,'inventory',1,0)) +
                     max(decode(b.skill_code,'accounting',1,0))) >= 1)
order by candidateID,skill_code;

--■■■分析関数を使わない方法3■■■
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 max(decode(b.skill_code,'manufacturing',1,0)) *
                    (max(decode(b.skill_code,'inventory',1,0)) +
                     max(decode(b.skill_code,'accounting',1,0))) >= 1)
order by candidateID,skill_code;

--■■■case式でin述語を使い論理和を求める方法■■■
select candidateID,skill_code
from (select candidateID,skill_code,
      max(decode(skill_code,'manufacturing',1,0)) over(partition by candidateID) as manufacturing,
      max(case when skill_code in('inventory','accounting') then 1 else 0 end)
      over(partition by candidateID) as inventory_accounting
        from CandidateSkills)
where manufacturing * inventory_accounting = 1
order by candidateid,skill_code;

解説

分析関数を使わない方法では、
having句で、ブール値で論理演算を行ってます。

ブール代数では、1+1=1ですが
数値の演算では、1+1=2
となるので、最終的に1以上を真と判定する必要があります。

数値をブール代数として演算するのではなく、
where manufacturing*(inventory+accounting) >= 1
を
where manufacturing = 1 and (inventory = 1 or accounting = 1)

および
having max(decode(b.skill_code,'manufacturing',1,0)) *
      (max(decode(b.skill_code,'inventory',1,0)) +
       max(decode(b.skill_code,'accounting',1,0))) >= 1
を
having max(decode(b.skill_code,'manufacturing',1,0)) = 1 and
      (max(decode(b.skill_code,'inventory',1,0)) = 1 or
       max(decode(b.skill_code,'accounting',1,0)) = 1)

としてもいいかもしれません。
数値をブール代数として演算するほうが、分かりやすいと思いますが・・・

また、case式でin述語を使い論理和を求めてもいいでしょう。

---

CodeZine：分析関数の衝撃（完結編）

---