10-29 要素数が1の集合同士の直積

SQLパズル

商品テーブル
商品NO     商品名   単価
------   --------   ----
   100     レモン     80
   200   アップル    100

売上テーブル
売上NO   商品NO    数量
------   ------   ----
     1      100     10
     2      100     20
     3      100     30
     4      200     25
     5      200     50
     6      200     80

各商品毎の売上合計を以下の形式で出力する。

出力結果
レモン  レモン売上合計   アップル  アップル売上合計
------  --------------  -------  ---------------
レモン           4,800  アップル           15,500

データ作成スクリプト

create table 商品(
商品NO number(3),
商品名 varchar2(8),
単価   number(3));

insert into 商品 values(100,'レモン'  , 80);
insert into 商品 values(200,'アップル',100);

create table 売上(
売上NO number(1),
商品NO number(3),
数量   number(2));

insert into 売上 values(1,100,10);
insert into 売上 values(2,100,20);
insert into 売上 values(3,100,30);
insert into 売上 values(4,200,25);
insert into 売上 values(5,200,50);
insert into 売上 values(6,200,80);
commit;

SQL

col レモン売上合計 for a20
col アップル売上合計 for a20

--■■■要素数が1の集合同士で直積演算させる方法■■■
select レモン,to_char(レモン売上合計,'999,999') as レモン売上合計,
アップル,to_char(アップル売上合計,'999,999') as アップル売上合計
from (select 'レモン' as レモン,
      単価 * (select sum(b.数量) from 売上 b
               where b.商品NO=a.商品NO) as レモン売上合計
        from 商品 a
       where 商品名 = 'レモン'),
     (select 'アップル' as アップル,
      単価 * (select sum(b.数量) from 売上 b
               where b.商品NO=a.商品NO) as アップル売上合計
        from 商品 a
       where 商品名 = 'アップル');

--■■■結合を使う方法■■■
select 'レモン',
to_char(sum(decode(a.商品名,'レモン',b.数量*a.単価,0)),'999,999') as レモン売上合計,
'アップル',
to_char(sum(decode(a.商品名,'アップル',b.数量*a.単価,0)) ,'999,999') as アップル売上合計
from 商品 a,売上 b
where a.商品NO=b.商品NO;

解説

要素数が1の集合同士で直積演算させた集合の要素数は、
1となります(1*1=1)