每天累计sql

drop table test1;
drop table test2;
delete from test1;
delete from test2;


create table test1(id integer, name varchar2(20), score integer, stat_date varchar2(8));
insert into test1 values(1,'xiaohong',60,'20120914');
insert into test1 values(1,'xiaohong',70,'20120915');
insert into test1 values(2,'xiaocui',80,'20120914');
insert into test1 values(2,'xiaocui',90,'20120915');

create table test2(id integer, name varchar2(20), all_score integer, stat_date varchar2(8));

declare
begin
insert into test2
(
    id,
    name,
    all_score,
    stat_date
)
select
    t1.id,
    t1.name,
    case when t1.score is not null then t1.score else 0 end
    +
    case when t2.all_score is not null then t2.all_score else 0 end,
    t1.stat_date
from test1 t1 left join test2 t2 on t1.id=t2.id and t1.name=t2.name and t2.stat_date='20120914' where t1.stat_date='20120915';
end;


select *from test1;
select *from test2;