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  
//