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

//