10-280 model句によるレポート表作成

SQLパズル

YesNoTable
ColA  ColB
----  ----
yes   no
no    yes
no    yes
no    yes
yes   yes
no    no
yes   yes
yes   no

yesのカウントと
Noのカウントを
下記の形で出力する。

出力結果
YesNo  CntA  CntB
-----  ----  ----
yes    4     5
no     4     3

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

データ作成スクリプト

create table YesNoTable(ColA,ColB) as
select 'yes' , 'no'  from dual union all
select 'no'  , 'yes' from dual union all
select 'no'  , 'yes' from dual union all
select 'no'  , 'yes' from dual union all
select 'yes' , 'yes' from dual union all
select 'no'  , 'no'  from dual union all
select 'yes' , 'yes' from dual union all
select 'yes' , 'no'  from dual;

SQL

--■■■model句を使う方法(10g以降)■■■
select soeji as YesNo,cntA,cntB
  from YesNoTable
 model return updated rows
 dimension by(cast(RowNum as varchar2(3)) as soeji)
 measures(ColA,ColB,0 as cntA,0 as cntB)
rules(
cntA['yes'] = count(decode(ColA,'yes',1))[any],
cntB['yes'] = count(decode(ColB,'yes',1))[any],
cntA['no']  = count(decode(ColA,'no' ,1))[any],
cntB['no']  = count(decode(ColB,'no' ,1))[any]);

--■■■union allを使う方法■■■
select 'yes' as YesNo,
count(decode('yes',ColA,1)) as cntA,
count(decode('yes',ColB,1)) as cntB
  from YesNoTable
union all
select 'no',
count(decode('no',ColA,1)) as cntA,
count(decode('no',ColB,1)) as cntB
  from YesNoTable;

--■■■外部結合を使う方法■■■
select a.YesNo,
count(decode(a.YesNo,b.ColA,1)) as cntA,
count(decode(a.YesNo,b.ColB,1)) as cntB
from (select 'yes' as YesNo from dual union all
      select 'no' from dual) a
Left Join YesNoTable b
  on a.YesNo in(b.ColA,b.ColB)
group by a.YesNo;

解説

model句の使いどころかもしれませんねぇ

---