-
//将当前行某列的值与前面所有行的此列值相加,即累计求和://方法一:with t as(select 1 val from dual union allselect 3 from dual union allselect 5 from dual union allselect 7 from dual union allselect 9 from dual)select val,sum(val)over (order by rownum rows between unbounded preceding and current row)sum_valfrom tgroup by rownum,valorder by rownum;VAL SUM_VAL---------- ----------1 13 45 97 169 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 allselect 3 from dual union allselect 5 from dual union allselect 7 from dual union allselect 9 from dual),cte_2 as(select rownum rn,val from cte_1)select a.val , sum(b.val) sum_valfrom cte_2 a , cte_2 bwhere b.rn <= a.rngroup by a.val///方法三://创建一个递归函数,求和//f(n) = x + f(n-1)create table tasselect 1 id,1 val from dual union allselect 2,3 from dual union allselect 3,5 from dual union allselect 4,7 from dual union allselect 5,9 from dual/create or replace function fun_recursion(x in int)return integer isn integer :=0;beginselect val into nfrom twhere id=x;if x=1 thenreturn n;elsereturn n + fun_recursion(x-1);end if;exceptionwhen others thendbms_output.put_line(sqlerrm);end fun_recursion;/select val,fun_recursion(id) sum_val from t;VAL SUM_VAL---------- ----------1 13 45 97 169 25//