10-30 行を指定単位で分割

SQLパズル

注文テーブル
商品名  注文数量
------  --------
     A       500
     A      1000
     B       700
     B       200
     B       900
     C      1500

発注単位テーブル
商品名  単位
------  ----
     A   800
     B  2000
     C   300

商品単位で発注を行う。
但し、商品には発注単位があり、
発注単位の倍数単位にまとめて出力する。

出力結果
商品名  注文数量
------  --------
     A       800
     A       800
     B      2000
     C       300
     C       300
     C       300
     C       300
     C       300

データ作成スクリプト

create table 注文(
商品名   char(1),
注文数量 number(4));

insert into 注文 values('A', 500);
insert into 注文 values('A',1000);
insert into 注文 values('B', 700);
insert into 注文 values('B', 200);
insert into 注文 values('B', 900);
insert into 注文 values('C',1500);

create table 発注単位(
商品名 char(1),
単位   number(4));

insert into 発注単位 values('A', 800);
insert into 発注単位 values('B',2000);
insert into 発注単位 values('C', 300);
commit;

SQL

--■■■方法1■■■
select 商品名,注文数量
from (select 商品名,合計,
      Row_Number() over(partition by 商品名 order by 1) as Row_Num,
      (select b.単位 from 発注単位 b
        where b.商品名=a.商品名) as 注文数量
        from (select 商品名,sum(注文数量) as 合計
                from 注文
              group by 商品名) a,all_catalog)
where Row_Num <= ceil(合計/注文数量);

--■■■方法2■■■
select aa.商品名,aa.単位
from (select a.商品名,b.単位,
      ceil(sum(a.注文数量)/b.単位) as 行数
        from 注文 a,発注単位 b
       where a.商品名 = b.商品名
      group by a.商品名,b.単位) aa,
     (select RowNum as Counter from dict) bb
 where aa.行数 >= bb.Counter
order by aa.商品名;

解説

Row_Number() over(partition by 商品名 order by 1)で、
商品名ごとに、1からの自然数の連番を作成してます。

---