9-66 SQLで条件法

SQLパズル

Testテーブル
ID   subject  Val
---  -------  ---
100  math     100
100  English   80
100  science   80
200  math      80
200  science   95
300  math      40
300  English   90
300  science   55
400  math      80
500  science   30
700  science   30
700  null      20

mathが80以上かmathがない
かつ
Englishが50以上かEnglishがない
IDを求める。

出力結果
ID
---
100
200
400
500
700

達人に学ぶ SQL徹底指南書の15ページ,147ページを参考にさせていただきました

データ作成スクリプト

create table Test(ID,subject,Val) as
select 100,'math'   ,100 from dual union all
select 100,'English', 80 from dual union all
select 100,'science', 80 from dual union all
select 200,'math'   , 80 from dual union all
select 200,'science', 95 from dual union all
select 300,'math'   , 40 from dual union all
select 300,'English', 90 from dual union all
select 300,'science', 55 from dual union all
select 400,'math'   , 80 from dual union all
select 500,'science', 30 from dual union all
select 700,'science', 30 from dual union all
select 700,null     , 20 from dual;

SQL

--■■■2値論理の条件法(subjectのnullを考慮しない)を使う方法■■■
--select ID
--  from Test
--group by ID
--having min(case when (subject != 'math'    or Val >= 80)
--                 and (subject != 'English' or Val >= 50)
--                 then 1 else 0 end) = 1;

--■■■3値論理の条件法(subjectのnullを考慮)を使う方法1■■■
select ID
  from Test
group by ID
having min(case when lnnvl(subject='math')    or Val >= 80
                 and lnnvl(subject='English') or Val >= 50
                then 1 else 0 end) = 1;

--■■■3値論理の条件法(subjectのnullを考慮)を使う方法2■■■
select ID
  from Test
group by ID
having min(case when Val >= 80 then 1
                when subject='math' then 0 else 1 end)
     * min(case when Val >= 50 then 1
                when subject='English' then 0 else 1 end) = 1;

--■■■3値論理の条件法(subjectのnullを考慮)を使う方法3■■■
select ID
  from Test
group by ID
having min(case when Val >= 80 then 1
                when subject='math' then 0
                when Val >= 50 then 1
                when subject='English' then 0 else 1 end) = 1;

解説

2値論理の条件法か
3値論理の条件法かで、複雑さが大きく変わります。

条件法
PならばQは、
P is not true OR Q is true
だからです。

ちなみに、3値論理での、
P is not true OR Q is trueの真理値表は、下記となります。

T True
F False
U Unknown

P  Q  P is not true OR Q is true
-  -  --------------------------
T  T    T
T  F    F
T  U    U
F  T    T
F  F    T
F  U    T
U  T    T
U  F    T
U  U    T

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
lnnvl述語が使えないパターンの別例も考えてみましょう。

Aが10かつBが20なら、Cが30な行を出力

create table Not_Lnnvl(A,B,C) as
select   10,  20,30 from dual union all
select   10,  20,99 from dual union all
select   99,  20,30 from dual union all
select   99,  99,30 from dual union all
select   99,  99,99 from dual union all
select null,  99,99 from dual union all
select   99,null,99 from dual union all
select null,null,99 from dual;

select A,B,C
  from Not_Lnnvl
 where case when C=30 then 1
            when A=10 and B=20 then 0
            else 1 end = 1;

条件法PならばQは、
P is not true OR Q is true
となります。これをブール代数の交換法則により
Q is true OR P is not true
として、
Q is true を最初のwhen句で判断
次のP is not true
をelse句に遷移したかで判断してます。

---