oracle累计求和

oracle累计求和

//将当前行某列的值与前面所有行的此列值相加,即累计求和:

//方法一：

with t as(

select 1 val from dual union all

select 3 from dual union all

select 5 from dual union all

select 7 from dual union all

select 9 from dual)

select val,

sum(val)

over (order by rownum rows between unbounded preceding and current row)

sum_val

from t

group by rownum,val

order by rownum;

VAL SUM_VAL

---------- ----------

1 1

3 4

5 9

7 16

9 25

//解析：

//sum(val)计算累积和;

//order by rownum 按照伪列rownum对查询的记录排序;

//between unbounded preceding and current row:定义了窗口的起点和终点;

//unbounded preceding:窗口的起点包括读取到的所有行;

//current row:窗口的终点是当前行，默认值，可以省略;

//

//方法二:

with cte_1 as(

select 1 val from dual union all

select 3 from dual union all

select 5 from dual union all

select 7 from dual union all

select 9 from dual

)

,cte_2 as(

select rownum rn,val from cte_1

)

select a.val,sum(b.val) sum_val

from cte_2 a,cte_2 b

where b.rn <= a.rn

group by a.val

/

//方法三：

//创建一个递归函数，求和

//f(n) = x + f(n-1)

create table t

as

select 1 id,1 val from dual union all

select 2,3 from dual union all

select 3,5 from dual union all

select 4,7 from dual union all

select 5,9 from dual

/

create or replace function fun_recursion(x in int)

return integer is

n integer :=0;

begin

select val into n

from t

where id=x;

if x=1 then

return n;

else

return n + fun_recursion(x-1);

end if;

exception

when others then

dbms_output.put_line(sqlerrm);

end fun_recursion;

/

select val,fun_recursion(id) sum_val from t;

VAL SUM_VAL

---------- ----------

1 1

3 4

5 9

7 16

9 25

//

参考文档

http://www.cnblogs.com/scottckt/archive/2012/10/11/2719958.html

http://blog.csdn.net/wang_yunj/article/details/51040029

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!