トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
10-127 クロス集計して空白行を追加
SQLパズル
DENテーブル
CD EDA KIN
----- --- ----
00001 1 100
00001 2 200
00001 3 300
00002 1 10
00002 2 20
00002 3 30
00002 4 40
00002 5 50
00002 6 60
(1) 1ページは5明細
(2) ソート順はCD,EDA順
(3) CDが変わった場合には改ページ
(4) CDごとの最後のデータの次の行には、CDのKINの合計のための小計行を出力
(5) 最後のCDの小計のあとに、全レコードのKINの合計を出力
(6) 1ページが5明細に満たない場合は、5明細になるように空白行を出力
(7) 各レコードは総ページ数、ページ数及び何行目かのデータを持つ
出力結果
CD EDA KIN 行 PAGE 総ページ数
----- ---- ---- -- ---- ----------
00001 1 100 1 1 3
00001 2 200 2 1 3
00001 3 300 3 1 3
小計 NULL 600 4 1 3
NULL NULL NULL 5 1 3
00002 1 10 1 2 3
00002 2 20 2 2 3
00002 3 30 3 2 3
00002 4 40 4 2 3
00002 5 50 5 2 3
00002 6 60 1 3 3
小計 NULL 210 2 3 3
合計 NULL 810 3 3 3
NULL NULL NULL 4 3 3
NULL NULL NULL 5 3 3
データ作成スクリプト
create table DEN(
CD char(5),
EDA number(1),
KIN number(3),
primary key(CD, EDA));
insert into DEN values('00001',1,100);
insert into DEN values('00001',2,200);
insert into DEN values('00001',3,300);
insert into DEN values('00002',1,10);
insert into DEN values('00002',2,20);
insert into DEN values('00002',3,30);
insert into DEN values('00002',4,40);
insert into DEN values('00002',5,50);
insert into DEN values('00002',6,60);
commit;
SQL
--■■■表関数を使う方法■■■
create or replace Package Pack10_127 Is
type PrintType is record(
CD DEN.CD%type,
EDA DEN.EDA%type,
KIN DEN.KIN%type,
LINE number(1),
PAGE number(1));
type PrintTypeSet is table of PrintType;
end;
/
create or replace function PrintRecord return Pack10_127.PrintTypeSet PipeLined IS
現在行 pls_Integer := 1;
out_rec Pack10_127.PrintType;
begin
for rec in (select CD,EDA,sum(KIN) as KIN,
Lead(CD) over(order by CD,EDA) as LeadCD,
grouping(CD) as groupingCD,
grouping(EDA) as groupingEDA
from DEN
group by RollUp(CD,EDA)
order by CD,EDA) Loop
out_rec.CD := case when rec.groupingCD = 1 then '合計'
when rec.groupingEDA = 1 then '小計'
else rec.CD end;
out_rec.EDA := rec.EDA;
out_rec.KIN := rec.KIN;
out_rec.LINE := case mod(現在行,5) when 0 then 5 else mod(現在行,5) end;
out_rec.PAGE := ceil(現在行/5);
pipe row(out_rec);
現在行 := 現在行+1;
if rec.CD != rec.LeadCD or rec.groupingCD = 1 then
while mod(現在行,5) != 1 Loop
out_rec.CD := null;
out_rec.EDA := null;
out_rec.KIN := null;
out_rec.LINE := case mod(現在行,5) when 0 then 5 else mod(現在行,5) end;
out_rec.PAGE := ceil(現在行/5);
pipe row(out_rec);
現在行 := 現在行+1;
end loop;
end if;
end loop;
end;
/
sho err
select a.*,count(*) over()/5 as 総ページ数 from table(PrintRecord) a;
--■■■select文を使う方法■■■
select decode(b.Counter,1,a.CD) as CD,
decode(b.Counter,1,a.EDA) as EDA,
decode(b.Counter,1,a.KIN) as KIN,
case mod(Row_Number() over(order by a.ソート用CD,b.Counter,a.EDA,decode(a.CD,'小計',1,'合計',2)),5)
when 0 then 5
else mod(Row_Number() over(order by a.ソート用CD,b.Counter,a.EDA,decode(a.CD,'小計',1,'合計',2)),5) end as 行,
ceil(Row_Number() over(order by a.ソート用CD,b.Counter,a.EDA,decode(a.CD,'小計',1,'合計',2))/5) as PAGE,
count(*) over()/5 as 総ページ数
from (select
case when grouping(CD) = 1 then max(CD) over() else CD end as ソート用CD,
count(EDA) over(partition by CD) as 同一コード件数,
case when grouping(CD) = 1 then '合計'
when grouping(EDA) = 1 then '小計'
else CD end as CD,
EDA,sum(KIN) as KIN,
Row_Number() over(order by CD desc nulls Last,EDA nulls first) as 最後の小計
from DEN
group by RollUp(CD,EDA)) a,
(select RowNum as Counter from all_catalog where RowNum <= 5) b
where (a.CD !='小計' and b.Counter = 1)
or (a.CD ='小計' and a.最後の小計 != 1 and b.Counter <= 5-mod(a.同一コード件数,5))
or (a.CD ='小計' and a.最後の小計 = 1 and b.Counter <= 5-mod(a.同一コード件数+1,5))
order by a.ソート用CD,b.Counter,a.EDA,decode(a.CD,'小計',1,'合計',2);
解説
クロス集計の結果に対して、
空白行を追加してます
case mod(Row_Number() over(order by a.ソート用CD,b.Counter,a.EDA,decode(a.CD,'小計',1,'合計',2)),5)
when 0 then 5
else mod(Row_Number() over(order by a.ソート用CD,b.Counter,a.EDA,decode(a.CD,'小計',1,'合計',2)),5) end as 行,
は
nvl(nullif(
mod(Row_Number() over(order by a.ソート用CD,b.Counter,a.EDA,decode(a.CD,'小計',1,'合計',2)),5)
,0),5) as 行,
で書き換えることができますが、
逆に分かりにくいかもしれません