10-227 存在肯定命題の真偽の論理積

SQLパズル

clientsTable            resultsTable
ID  Name  address       ID  Code   Val
--  ----  --------      --  ----  ----
 1  abc   1234 aaa       1  CLD     50
 2  cde   4567 bbb       1  CLF     40
 3  xyz   7890 ccc       1  CLS     30
 4  efg   2345 ddd       2  CLD     10
 5  ijk   3456 eee       2  CLF     40
 6  ufj   9999 fff       2  CLS     40
                         3  CLD     60
                         3  CLF     10
                         3  CLS     20
                         4  CLD   null
                         4  CLF     40
                         4  CLS     40
                         5  CLF     40
                         5  CLS     40

clientsテーブルのIDと、
resultsテーブルのIDとを紐付けて、
以下の3つの条件を3つとも満たす行が存在する、
clientsテーブルのレコードを出力する。

条件1 Code = 'CLS' and Val < 45
条件2 Code = 'CLD' and Val > 25
条件3 Code = 'CLF' and Val < 50

出力結果
ID  Name  address
--  ----  --------
 1  abc   1234 aaa
 3  xyz   7890 ccc

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

データ作成スクリプト

create table clientsTable(ID,Name,address) as
select 1,'abc','1234 aaa' from dual union
select 2,'cde','4567 bbb' from dual union
select 3,'xyz','7890 ccc' from dual union
select 4,'efg','2345 ddd' from dual union
select 5,'ijk','3456 eee' from dual union
select 6,'ufj','9999 fff' from dual;

create table resultsTable(ID,Code,Val) as
select 1,'CLD',  50 from dual union all
select 1,'CLF',  40 from dual union all
select 1,'CLS',  30 from dual union all
select 2,'CLD',  10 from dual union all
select 2,'CLF',  40 from dual union all
select 2,'CLS',  40 from dual union all
select 3,'CLD',  60 from dual union all
select 3,'CLF',  10 from dual union all
select 3,'CLS',  20 from dual union all
select 4,'CLD',null from dual union all
select 4,'CLF',  40 from dual union all
select 4,'CLS',  40 from dual union all
select 5,'CLF',  40 from dual union all
select 5,'CLS',  40 from dual;

SQL

--■■■exists述語を使う方法■■■
select ID,Name,address
  from clientsTable a
 where exists(select 1
                from resultsTable b
               where b.ID = a.ID
              having max(case when b.Code = 'CLS' and b.Val < 45 then 1 else 0 end)
                   * max(case when b.Code = 'CLD' and b.Val > 25 then 1 else 0 end)
                   * max(case when b.Code = 'CLF' and b.Val < 50 then 1 else 0 end) = 1)
order by ID;

--■■■in述語を使う方法■■■
select ID,Name,address
  from clientsTable a
 where ID in (select ID
                from resultsTable
               group by ID
              having max(case when Code = 'CLS' and Val < 45 then 1 else 0 end)
                   * max(case when Code = 'CLD' and Val > 25 then 1 else 0 end)
                   * max(case when Code = 'CLF' and Val < 50 then 1 else 0 end) = 1)
order by ID;

解説

3つの存在肯定命題の真偽の、論理積が真かを判定してます。

---