oracle累计求和

我会带着你远行 2022-06-09 08:54 192阅读 0赞

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

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

oracle累计求和

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql 累计和_从MySQL表中获取累计和

相关 MySQL 生成累计差

相关 MySQL 生成累计乘积

相关 MySQL 生成累计和

相关高级报表之移动分析和累计求和

相关 JavaWeb注解类累计

相关 ztree点滴累计

相关 sql server 点滴累计

相关 oracle累计求和

相关 oracle 下实现累计求和