10-168 ブール値を求めて論理積演算

SQLパズル

ExamLogTable
slNO  name    testNO
----  ------  ------
   1  reni         1
   2  arun         2
   3  jithu        3
   4  rajesh       4
   5  shiju        1
   6  Thomas       1
   7  Thomas       2
   8  Thomas       3
   9  Thomas       4
  10  Jack         1
  11  Jack         1
  12  Jack         2
  13  Jack         2
  20  John         1
  21  John         2
  22  John         3
  23  John         4
  24  John         5

nameごとで、
testNOが1のレコード
testNOが2のレコード
testNOが3のレコード
testNOが4のレコード
がある、nameのレコードを出力する

出力結果
slNO  name        testNO
----  ----------  ------
   6  Thomas           1
   7  Thomas           2
   8  Thomas           3
   9  Thomas           4
  20  John             1
  21  John             2
  22  John             3
  23  John             4
  24  John             5

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

データ作成スクリプト

create table ExamLogTable as
select 1 as slNO,'reni' as name, 1 as testNO from dual
union select 2,'arun'  ,2 from dual
union select 3,'jithu' ,3 from dual
union select 4,'rajesh',4 from dual
union select 5,'shiju' ,1 from dual
union select 6,'Thomas',1 from dual
union select 7,'Thomas',2 from dual
union select 8,'Thomas',3 from dual
union select 9,'Thomas',4 from dual
union select 10,'Jack' ,1 from dual
union select 11,'Jack' ,1 from dual
union select 12,'Jack' ,2 from dual
union select 13,'Jack' ,2 from dual
union select 20,'John' ,1 from dual
union select 21,'John' ,2 from dual
union select 22,'John' ,3 from dual
union select 23,'John' ,4 from dual
union select 24,'John' ,5 from dual;

SQL

--■■■分析関数を使う方法1■■■
select slNO,name,testNO
from (select slNO,name,testNO,
      max(case when testNO=1 then 1 else 0 end) over(partition by name) as "has1",
      max(case when testNO=2 then 1 else 0 end) over(partition by name) as "has2",
      max(case when testNO=3 then 1 else 0 end) over(partition by name) as "has3",
      max(case when testNO=4 then 1 else 0 end) over(partition by name) as "has4"
        from ExamLogTable)
where "has1" * "has2" * "has3" * "has4" = 1
order by slNO;

--■■■分析関数を使う方法2■■■
select slNO,name,testNO
from (select slNO,name,testNO,
      count(distinct case when testNO in(1,2,3,4) then testNO end)
      over(partition by name) as distinctTestCount
        from ExamLogTable)
where distinctTestCount = 4
order by slNO;

--■■■分析関数を使わない方法■■■
select slNO,name,testNO
  from ExamLogTable a
 where name in(select b.name
                 from ExamLogTable b
                where b.testNO in(1,2,3,4)
               group by b.name
               having count(distinct b.testNO) = 4)
order by slNO;

解説

分析関数を使う方法1では、
max(case when 条件A then 1 else 0 end)は、
集合に、条件Aを満たす要素が存在すれば1、
集合に、条件Aを満たす要素が存在しなければ0
という考え方を使って、ブール値を求めて
掛け算で論理積演算を行ってます。

9-42 存在有無のブール値の応用

---