-
//将当前行某列的值与前面所有行的此列值相加,即累计求和://方法一: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//
相关文章
- Oracle常用知识小结
- oracle 10g\11g用imp导入数据的时候报错:ORA-01658: 无法为表空间 MAXDATA 中的段创建 INITIAL 区 错误解决
- Oracle还原dmp文件/Oracle快速还原dmp文件
- Storm累计求和进群运行代码
- C# 读取oracle 中文乱码的解决方案
- Windows 10上安装Oracle 11g报错: [INS-30131] 执行安装程序验证所需的初始设置失败。无法从节点 “zb-m2004-05243“ 检索 exectask 的版本
- Oracle期末保命宝典
- 红帽取代 Oracle,接管 OpenJDK 8 和 OpenJDK 11 | 极客头条
- ORACLE中常见的几种锁
- Actifio中如何分析Oracle备份恢复的报错