oracle分析以下数据表,用两种办法(视图及存储过程)实现累计列Acc的运 ...
发布网友
共3个回答
热心网友
视图：
with t1 as ( select 1 as id,'一车队' name, '1' month, 10 amount from al
union all select 2,'一车队', '2', 20 from al
union all select 3,'一车队', '3', 40 from al
union all select 4,'机动车队', '1', 30 from al
union all select 5,'机动车队', '2', 20 from al
union all select 6,'机动车队', '3', 50 from al)
select t1.*,sum(amount) over(partition by name order by name , month) from t1 ;
过程：
create or replace procere pro_sum is
l_sum_acc number ;
begin
for rec in (select distinct (name) nn from xxx) loop
dbms_output.put_line('xxxxxxx') ;
l_sum_acc := 0 ;
for rec2 in (select amount from xxx where name=rec.nn) ;
l_sum_acc := l_sum_acc + rec2.amount ;
dbms_output.put_line('xxxxx') ;
end loop ;
end loop ;
dbms_output.put_line(l_sum_acc) ;
end;
你的数据不规范，month应该是数字或者date类型的数据，不然这样累加起来没法找到规律。
热心网友
with t0 as (select 1 id, '一' month from al
union select 2, '二' from al
union select 3, '三' from al)
,t1 as ( select '一车队' name, '一' month, 10 amount from al
union all select '一车队', '二', 20 from al
union all select '一车队', '三', 40 from al
union all select '机动车队', '一', 30 from al
union all select '机动车队', '二', 20 from al
union all select '机动车队', '三', 50 from al)
,t2 as (select t1.*, t0.id from t1 join t0 on t1.month=t0.month)
select t2.name,t2.month,t2.amount, (select sum(amount) from t2 t3 where t3.name=t2.name and t3.id<=t2.id) acc from t2
热心网友
方法1
select id,name ,moth ,amount,(select sum(amount) from tab subt where subt.name = t.name and subt.id <= t.id ) acc from tab t ;
方法2
select t1.id,t1.name ,t1.moth ,t1.amount ,sum(case when( t1.name = t2.name and t1.id >= t2.id ) then amount else 0 end ) acc
from tab t1,tab t2
group by t1.id,t1.name ,t1.moth ,t1.amount
