9-44 順序無視でグループ化(3列)

SQLパズル

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

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

出力結果
style_A  style_B  style_C  tally
-------  -------  -------  -----
      A        A        A      2
      A        A        B    150
      A        A        C    608
      A        B        B    470
      A        B        C   2050
      A        C        C    790
      B        B        B    300
      B        B        C   1580
      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,
tally   number(3) not null);

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

SQL

select style_A,style_B,style_C,sum(tally) as tally
 from (select Least(style_A,style_B,style_C) as style_A,
       case Least(style_A,style_B,style_C)
       when style_A then Least(style_B,style_C)
       when style_B then Least(style_A,style_C)
       when style_C then Least(style_A,style_B) end as style_B,
       greatest(style_A,style_B,style_C) as style_C,
       tally
        from SalesSlips)
group by style_A,style_B,style_C
order by style_A,style_B,style_C;

解説

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

---

場合分けの資料

---