10-184 存在肯定命題の真偽と、要素数のチェック

SQLパズル

packageTable
ID  pack
--  ----
 1  CD
 1  DVD
 1  TV
 2  CD
 3  TV
 4  CD
 4  DVD
 4  TV
 4  PC
 4  PS
 4  PS2
 5  DVD
 5  PC

IDごとで、
packにTVがあって、
レコード数が4未満のIDの行を出力する。

出力結果
ID  pack
--  ----
 1  CD
 1  DVD
 1  TV
 3  TV

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

データ作成スクリプト

create table packageTable as
select 1 as ID,'CD' as pack from dual
union select 1,'DVD' from dual
union select 1,'TV'  from dual
union select 2,'CD'  from dual
union select 3,'TV'  from dual
union select 4,'CD'  from dual
union select 4,'DVD' from dual
union select 4,'TV'  from dual
union select 4,'PC'  from dual
union select 4,'PS'  from dual
union select 4,'PS2' from dual
union select 5,'DVD' from dual
union select 5,'PC'  from dual;

SQL

--■■■分析関数を使う方法■■■
select ID,pack
from (select ID,pack,
      max(case when pack = 'TV'
               then 1
               else 0 end) over(partition by ID) as hasTV,
      count(*) over(partition by ID) as RecordCount
        from packageTable)
where hasTV = 1
  and RecordCount < 4
order by ID,pack;

--■■■分析関数を使わない方法(inを使用)■■■
select ID,pack
  from packageTable
 where ID in(select ID
               from packageTable
             group by ID
             having max(case when pack = 'TV'
                             then 1
                             else 0 end) = 1
                and count(*) < 4)
order by ID,pack;

--■■■分析関数を使わない方法(existsを使用)■■■
select ID,pack
  from packageTable a
 where exists(select 1
                from packageTable b
               where b.ID = a.ID
              having max(case when pack = 'TV'
                              then 1
                              else 0 end) = 1
                 and count(*) < 4)
order by ID,pack;

解説

存在肯定命題の真偽をmax関数で求め、
count関数で要素数で求めています。

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

---