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://topic.youkuaiyun.com/u/20110503/00/f518c144-5ae4-4843-bf54-056159b6f073.html?3783

关于unbounded preceding,参考:

[1].Order by range unbounded preceding

http://www.java2s.com/Tutorial/Oracle/0320__Analytical-Functions/Orderbyrangeunboundedpreceding.htm

[2].Order by, range unbounded preceding

http://www.java2s.com/Code/Oracle/Select-Query/Orderbyrangeunboundedpreceding.htm