トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
7-54 引き当て処理
SQLパズル
請求テーブル
Code 請求額 入金額 請求残
---- ------ ------ ------
1 50 50 0
2 100 80 20
3 200 0 200
4 300 10 290
5 400 90 310
請求テーブルに、250円の入金があった場合の結果を出力する。
(入金はCodeの昇順に引き当てる)
出力結果
Code 請求額 入金額 請求残
---- ------ ------ ------
1 50 50 0
2 100 100 0
3 200 200 0
4 300 40 260
5 400 90 310
データ作成スクリプト
create table 請求(
Code number(1),
請求額 number(3),
入金額 number(3),
請求残 number(3));
insert into 請求 values(1, 50,50, 0);
insert into 請求 values(2,100,80, 20);
insert into 請求 values(3,200, 0,200);
insert into 請求 values(4,300,10,290);
insert into 請求 values(5,400,90,310);
commit;
SQL
--■■■相関サブクエリを使う方法■■■
with WorkView as (
select Code,請求額,入金額,請求残,
sum(請求残) over(order by Code) as 請求残累計
from 請求)
select Code,請求額,
case when 請求残累計 <= 250 then 請求額
when Code = (select min(Code) from WorkView b
where b.請求残累計 > 250) then 入金額+250-(請求残累計-請求残)
else 入金額 end as 入金額,
case when 請求残累計 <= 250 then 0
when Code = (select min(Code) from WorkView b
where b.請求残累計 > 250) then 請求残累計-250
else 請求残 end as 請求残
from WorkView;
--■■■min関数を使う方法■■■
select Code,請求額,
case when 請求残累計 <= 250 then 請求額
when Code = min(case when 請求残累計 > 250 then Code end) over()
then 入金額+250-(請求残累計-請求残) else 入金額 end as 入金額,
case when 請求残累計 <= 250 then 0
when Code = min(case when 請求残累計 > 250 then Code end) over()
then 請求残累計-250 else 請求残 end as 請求残
from (select Code,請求額,入金額,請求残,
sum(請求残) over(order by Code) as 請求残累計
from 請求);
--■■■First_Value関数とignore nullsを使う方法(10g以降)■■■
select Code,請求額,
case when 請求残累計 <= 250 then 請求額
when Code = First_Value(case when 請求残累計 > 250 then Code end ignore nulls)
over(order by Code)
then 入金額+250-(請求残累計-請求残) else 入金額 end as 入金額,
case when 請求残累計 <= 250 then 0
when Code = First_Value(case when 請求残累計 > 250 then Code end ignore nulls)
over(order by Code)
then 請求残累計-250 else 請求残 end as 請求残
from (select Code,請求額,入金額,請求残,
sum(請求残) over(order by Code) as 請求残累計
from 請求);
--■■■表関数を使う方法■■■
create or replace Package Pack07_54 Is
type PrintType is record(
Code 請求.Code%type,
請求額 請求.請求額%type,
入金額 請求.入金額%type,
請求残 請求.請求残%type);
type PrintTypeSet is table of PrintType;
end;
/
create or replace function 請求Record(hiki入金 number) return Pack07_54.PrintTypeSet PipeLined is
out_rec Pack07_54.PrintType;
引当額 number(3);
入金 number(3) := hiki入金;
begin
for rec in (select Code,請求額,入金額,請求残
from 請求
order by Code) Loop
out_rec.Code := rec.Code;
out_rec.請求額 := rec.請求額;
if 入金 > 0 and rec.請求残 > 0 then
if rec.請求残 < 入金 then
引当額 := rec.請求残;
入金 := 入金 - rec.請求残;
else
引当額 := 入金;
入金 := 0;
end if;
out_rec.入金額 := rec.入金額+引当額;
out_rec.請求残 := rec.請求残-引当額;
else
out_rec.入金額 := rec.入金額;
out_rec.請求残 := rec.請求残;
end if;
pipe row(out_rec);
end Loop;
end;
/
sho err
select * from table(請求Record(250));
解説