トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

10-173 全称命題と存在命題で論理積(group化版)

SQLパズル

personTable1
person  ID_State
------  --------
Carl           3
Carl           4
Joe            3
Mark           2
Mark           3
Mark           4
Paul           1
Paul           2
Sam            2
Sam            3
Wendy          2
Wendy          2
Wendy          3
Wendy          3
Wendy          4
John           1
John           2
John           2
John           3
Tom            1
Tom            2
Tom            3
Tom            4

personごとで、
ID_State=2の行が存在し、かつ、
ID_State=3の行が存在し、かつ、
ID_State=4の行が存在し、かつ、
全てのID_Stateが2,3,4のいずれか
であるpersonを出力する。

出力結果
person
------
Mark
Wendy

こちらを参考にさせていただきました(英語)


データ作成スクリプト

create table personTable1 as
select 'Carl' as person,3 as ID_State from dual
union all select 'Carl'  ,4 from dual
union all select 'Joe'   ,3 from dual
union all select 'Mark'  ,2 from dual
union all select 'Mark'  ,3 from dual
union all select 'Mark'  ,4 from dual
union all select 'Paul'  ,1 from dual
union all select 'Paul'  ,2 from dual
union all select 'Sam'   ,2 from dual
union all select 'Sam'   ,3 from dual
union all select 'Wendy' ,2 from dual
union all select 'Wendy' ,2 from dual
union all select 'Wendy' ,3 from dual
union all select 'Wendy' ,3 from dual
union all select 'Wendy' ,4 from dual
union all select 'John'  ,1 from dual
union all select 'John'  ,2 from dual
union all select 'John'  ,2 from dual
union all select 'John'  ,3 from dual
union all select 'Tom'   ,1 from dual
union all select 'Tom'   ,2 from dual
union all select 'Tom'   ,3 from dual
union all select 'Tom'   ,4 from dual;


SQL

--■■■having句でブール代数を使う方法■■■
select person
  from personTable1
group by person
having max(case when ID_STATE = 2 then 1 else 0 end)
     * max(case when ID_STATE = 3 then 1 else 0 end)
     * max(case when ID_STATE = 4 then 1 else 0 end)
     * min(case when ID_STATE in(2,3,4) then 1 else 0 end) = 1;

--■■■having句でブール代数を使わない方法■■■
select person
  from personTable1
group by person
having max(case when ID_STATE = 2 then 1 else 0 end) = 1
   and max(case when ID_STATE = 3 then 1 else 0 end) = 1
   and max(case when ID_STATE = 4 then 1 else 0 end) = 1
   and min(case when ID_STATE in(2,3,4) then 1 else 0 end) = 1;


解説

partitionを切って、もしくはgroup化しての
max(case when 条件P then 1 else 0 end) = 1 が∃X:P(X)に相当
min(case when 条件P then 1 else 0 end) = 1 が∀X:P(X)に相当
max(case when 条件P then 0 else 1 end) = 1 が∃X:¬P(X)に相当
min(case when 条件P then 0 else 1 end) = 1 が∀X:¬P(X)に相当
を使ってます。

all述語を使う方法は、分かりにくくなるでしょう。
1 = all(max(case when ID_STATE = 2 then 1 else 0 end),
        max(case when ID_STATE = 3 then 1 else 0 end),
        max(case when ID_STATE = 4 then 1 else 0 end),
        min(case when ID_STATE in(2,3,4) then 1 else 0 end))