9-45 順序無視でグループ化(4列)

SQLパズル

SalesSlipsテーブル
style_A  style_B  style_C  style_D  tally
-------  -------  -------  -------  -----
      A        A        A        A      2
      A        A        B        B      4
      A        A        C        A      8
      A        B        A        B     16
      A        B        B        A     30
      A        B        C        B     50
      A        C        A        A     70
      A        C        B        B     80
      A        C        C        A    100
      B        A        A        B    130
      B        A        B        A    180
      B        A        C        B    220
      B        B        A        A    260
      B        B        B        B    300
      B        B        C        A    340
      B        C        A        B    390
      B        C        B        A    460
      B        C        C        B    500
      C        A        A        A    530
      C        A        B        B    580
      C        A        C        A    690
      C        B        A        B    730
      C        B        B        A    780
      C        B        C        B    830

style_Aとstyle_Bとstyle_Cとstyle_Dの順序を無視した組み合わせごとの
tallyの合計を以下の形式で出力する

出力結果
style_A  style_B  style_C  style_D  tally
-------  -------  -------  -------  -----
      A        A        A        A      2
      A        A        A        C    608
      A        A        B        B    620
      A        A        C        C    790
      A        B        B        C   3630
      B        B        B        B    300
      B        B        C        C   1330

9-37 順序無視でグループ化(2列)のアレンジです

データ作成スクリプト

create table SalesSlips(
style_A char(1)   not null,
style_B char(1)   not null,
style_C char(1)   not null,
style_D char(1)   not null,
tally   number(3) not null);

insert into SalesSlips values('A','A','A','A',  2);
insert into SalesSlips values('A','A','B','B',  4);
insert into SalesSlips values('A','A','C','A',  8);
insert into SalesSlips values('A','B','A','B', 16);
insert into SalesSlips values('A','B','B','A', 30);
insert into SalesSlips values('A','B','C','B', 50);
insert into SalesSlips values('A','C','A','A', 70);
insert into SalesSlips values('A','C','B','B', 80);
insert into SalesSlips values('A','C','C','A',100);
insert into SalesSlips values('B','A','A','B',130);
insert into SalesSlips values('B','A','B','A',180);
insert into SalesSlips values('B','A','C','B',220);
insert into SalesSlips values('B','B','A','A',260);
insert into SalesSlips values('B','B','B','B',300);
insert into SalesSlips values('B','B','C','A',340);
insert into SalesSlips values('B','C','A','B',390);
insert into SalesSlips values('B','C','B','A',460);
insert into SalesSlips values('B','C','C','B',500);
insert into SalesSlips values('C','A','A','A',530);
insert into SalesSlips values('C','A','B','B',580);
insert into SalesSlips values('C','A','C','A',690);
insert into SalesSlips values('C','B','A','B',730);
insert into SalesSlips values('C','B','B','A',780);
insert into SalesSlips values('C','B','C','B',830);
commit;

SQL

--■■■場合分けを行い、単純case式を使う方法■■■
select style_A,style_B,style_C,style_D,sum(tally) as tally
 from (select Least(style_A,style_B,style_C,style_D) as style_A,
       case Least(style_A,style_B,style_C,style_D)
       when style_A then Least(style_B,style_C,style_D)
       when style_B then Least(style_A,style_C,style_D)
       when style_C then Least(style_A,style_B,style_D)
       when style_D then Least(style_A,style_B,style_C) end as style_B,
       case greatest(style_A,style_B,style_C,style_D)
       when style_A then greatest(style_B,style_C,style_D)
       when style_B then greatest(style_A,style_C,style_D)
       when style_C then greatest(style_A,style_B,style_D)
       when style_D then greatest(style_A,style_B,style_C) end as style_C,
       greatest(style_A,style_B,style_C,style_D) as style_D,
       tally
        from SalesSlips)
group by style_A,style_B,style_C,style_D
order by style_A,style_B,style_C,style_D;

--■■■unionしてRow_Number関数を使う方法■■■
select style_A,style_B,style_C,style_D,sum(tally) as tally
from (select
      max(decode(rank,1,style)) as style_A,
      max(decode(rank,2,style)) as style_B,
      max(decode(rank,3,style)) as style_C,
      max(decode(rank,4,style)) as style_D,
      sum(tally) as tally
         from (select Row_Number() over(partition by Row_ID order by style) as rank,
               Row_ID,style,tally
                 from (select RowID as Row_ID,style_A as style,tally from SalesSlips
                       union all
                       select RowID,style_B,0 from SalesSlips
                       union all
                       select RowID,style_C,0 from SalesSlips
                       union all
                       select RowID,style_D,0 from SalesSlips))
       group by Row_ID)
group by style_A,style_B,style_C,style_D
order by style_A,style_B,style_C,style_D;

解説

場合分けを行い、単純case式を使ってます

対象列が、5列以上となった場合は、
インラインビューをネストさせていくか、unionしてRow_Number関数を使うか
だと思います

---

場合分けの資料

---