9-18 存在有無のブール値を求める

SQLパズル

Projectsテーブル
WorkID  step  status
------  ----  ---------
 AA100     1  Completed
 AA100     2  Waiting
 AA100     3  Waiting
 AA200     1  Waiting
 AA200     2  Waiting
 AA300     1  Completed
 AA300     2  Completed
 AA400     1  Waiting
 AA400     2  Completed
 AA500     1  Completed
 AA600     1  Waiting

WorkIDごとで、
step =1かつstatus=Completedのレコードが存在し、
step!=1かつstatus=Completedのレコードが存在しない
WorkIDを出力する

出力結果
WorkID
------
 AA100
 AA500

SQLパズル(日本語版)のパズル10 [仕事の段取り] を参考にさせていただきました
SQLパズル 第2版のパズル11 [作業依頼] を参考にさせていただきました

データ作成スクリプト

create table Projects(
WorkID varchar2(5),
step   number(4),
status varchar2(9) not null check(status in('Completed','Waiting')));

insert into Projects values('AA100',1,'Completed');
insert into Projects values('AA100',2,'Waiting');
insert into Projects values('AA100',3,'Waiting');
insert into Projects values('AA200',1,'Waiting');
insert into Projects values('AA200',2,'Waiting');
insert into Projects values('AA300',1,'Completed');
insert into Projects values('AA300',2,'Completed');
insert into Projects values('AA400',1,'Waiting');
insert into Projects values('AA400',2,'Completed');
insert into Projects values('AA500',1,'Completed');
insert into Projects values('AA600',1,'Waiting');
commit;

SQL

--■■■not existsを使う方法■■■
select WorkID
  from Projects a
 where not exists(select 1 from Projects b
                   where b.WorkID = a.WorkID
                     and b.status='Completed'
                     and b.step != 1)
   and step = 1
   and status='Completed';

--■■■存在有無のブール値を求める方法1■■■
select WorkID
  from (select WorkID,step,status,
        max(case when step != 1 and status='Completed' then 1 else 0 end)
        over(partition by WorkID) as IsExist
          from Projects)
where step = 1
  and status='Completed'
  and IsExist = 0;

--■■■存在有無のブール値を求める方法2■■■
select WorkID
  from Projects
 group by WorkID
having max(case when step  = 1 and status='Completed' then 1 else 0 end) *
       min(case when step != 1 and status='Completed' then 0 else 1 end) = 1;

解説

case式とmax関数、min関数を組み合わせて、
存在有無のブール値を求めることができます

存在有無のブール値を求める方法2では、
max(case when step  = 1 and status='Completed' then 1 else 0 end)が、
step  = 1 and status='Completed'のデータが存在すること(存在すれば1、存在しなければ0のブール値)

min(case when step != 1 and status='Completed' then 0 else 1 end)が、
step != 1 and status='Completed'のデータが存在しないこと(存在しなければ1、存在すれば0のブール値)

となっていて掛け算で論理積を求めてます

---