- //将当前行某列的值与前面所有行的此列值相加,即累计求和:
- //方法一:
- 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.csdn.net/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
原文地址:http://blog.csdn.net/bobo12082119/article/details/6386142