7-57 case式と分析関数で存在チェック

SQLパズル

商品管理テーブル
管理番号  商品番号   フラグ
--------  --------  ------
       1  A01            0
       1  A01            1
       1  B01            0
       1  C01            0
       1  D01            1
       2  A01            0
       2  B01            0
       2  C01            0
       2  D01            1
       3  A01            1
       3  B01            0
       3  C01            0
       3  D01            1

管理番号ごとに、
商品コードがA01のフラグが1となっている管理番号の
全レコードを出力する。

出力結果
管理番号  商品番号   フラグ
--------  --------  ------
       1  A01            0
       1  A01            1
       1  B01            0
       1  C01            0
       1  D01            1
       3  A01            1
       3  B01            0
       3  C01            0
       3  D01            1

データ作成スクリプト

create table 商品管理(
管理番号 number(1),
商品番号 char(3),
フラグ   number(1));

insert into 商品管理 values(1,'A01',0);
insert into 商品管理 values(1,'A01',1);
insert into 商品管理 values(1,'B01',0);
insert into 商品管理 values(1,'C01',0);
insert into 商品管理 values(1,'D01',1);
insert into 商品管理 values(2,'A01',0);
insert into 商品管理 values(2,'B01',0);
insert into 商品管理 values(2,'C01',0);
insert into 商品管理 values(2,'D01',1);
insert into 商品管理 values(3,'A01',1);
insert into 商品管理 values(3,'B01',0);
insert into 商品管理 values(3,'C01',0);
insert into 商品管理 values(3,'D01',1);
commit;

SQL

--■■■existsを使う方法■■■
select 管理番号,商品番号,フラグ
from 商品管理 a
where exists(select 1 from 商品管理 b
              where b.管理番号 = a.管理番号
                and b.商品番号='A01'
                and b.フラグ=1)
order by 管理番号,商品番号,フラグ;

--■■■case式と分析関数で存在チェックを行う方法■■■
select 管理番号,商品番号,フラグ
from (select 管理番号,商品番号,フラグ,
      max(case when 商品番号='A01' and フラグ=1 then 1
          else 0 end) over(partition by 管理番号) as IsExists
      from 商品管理)
where IsExists = 1
order by 管理番号,商品番号,フラグ;

解説

case式と分析関数を組み合わせて、存在チェックができます。

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

---