oracle over原理,oracle over()函数技术详解

select deptno,ename,sal from emp order by deptno;

先来一个简单的，注意over(...)条件的不同，

使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,

注意over (order   by ename)如果没有order by 子句，求和就不是“连续”的

select deptno,

ename,

sal,

sum(sal) over(order by ename) step,  --连续求和

sum(sal) over() total,               --此处sum(sal) over () 等同于sum(sal)

100 * round(sal / sum(sal) over(), 4) rate

from emp;

-----------------------------------------------------------------------------------------------------

使用子分区查出各部门薪水连续的总和。注意按部门分区。注意over(...)条件的不同，

sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和

sum(sal) over (partition by deptno) 按部门求总和

sum(sal) over (order by deptno，ename) 不按部门“连续”求总和

sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。

select deptno,

ename,

sal,

sum(sal) over(partition by deptno order by ename) a,     --各部门的薪水"连续"求和

sum(sal) over(partition by deptno) b,                    --部门统计的总和，同一部门总和不变

100*round(sal/sum(sal) over(partition by deptno), 4) c,

sum(sal) over(order by deptno, ename) d,                 --所有部门的薪水"连续"求和

sum(sal) over () e,                   --此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和

100*round(sal/sum(sal) over (),4) f

from emp

-----------------------------------------------------------------------------------------------------

select deptno,

ename,

sal,

sum(sal) over(partition by deptno order by sal) dept_sum,

sum(sal) over(order by deptno, sal) sum

from emp;