?

over()函数是Oracle的分析函数：语法如下

函数名（[参数]） over（[分区子句][排序子句]）

?

分区子句：partition by

排序子句：order by

?

?

1.原表信息：

SQL> break on deptno skip 1??-- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal
??2??from emp
??3??order by deptno;

? ? DEPTNO ENAME? ?? ?? ?? ? SAL
---------- ---------- ----------
? ?? ???10 CLARK? ?? ?? ?? ?2450
? ?? ?? ???KING? ?? ?? ?? ? 5000
? ?? ?? ???MILLER? ?? ?? ???1300

? ?? ???20 SMITH? ?? ?? ?? ? 800
? ?? ?? ???ADAMS? ?? ?? ?? ?1100
? ?? ?? ???FORD? ?? ?? ?? ? 3000
? ?? ?? ???SCOTT? ?? ?? ?? ?3000
? ?? ?? ???JONES? ?? ?? ?? ?2975

? ?? ???30 ALLEN? ?? ?? ?? ?1600
? ?? ?? ???BLAKE? ?? ?? ?? ?2850
? ?? ?? ???MARTIN? ?? ?? ???1250
? ?? ?? ???JAMES? ?? ?? ?? ? 950
? ?? ?? ???TURNER? ?? ?? ???1500
? ?? ?? ???WARD? ?? ?? ?? ? 1250


已选择14行。



2.先来一个简单的，注意over(...)条件的不同，
使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,
注意over (order??by ename)如果没有order by 子句，求和就不是“连续”的，
放在一起，体会一下不同之处：

SQL> break on '' -- 取消数据分段显示
SQL> select deptno,ename,sal,
??2??sum(sal) over (order by ename) 连续求和,
??3??sum(sal) over () 总和,? ?? ?? ?? ?? ?? ?-- 此处sum(sal) over () 等同于sum(sal)
??4??100*round(sal/sum(sal) over (),4) "份额(%)"
??5??from emp
??6??/

? ? DEPTNO ENAME? ?? ?? ?? ? SAL? ?连续求和? ?? ? 总和? ? 份额(%)
---------- ---------- ---------- ---------- ---------- ----------
? ?? ???20 ADAMS? ?? ?? ?? ?1100? ?? ? 1100? ?? ?29025? ?? ? 3.79
? ?? ???30 ALLEN? ?? ?? ?? ?1600? ?? ? 2700? ?? ?29025? ?? ? 5.51
? ?? ???30 BLAKE? ?? ?? ?? ?2850? ?? ? 5550? ?? ?29025? ?? ? 9.82
? ?? ???10 CLARK? ?? ?? ?? ?2450? ?? ? 8000? ?? ?29025? ?? ? 8.44
? ?? ???20 FORD? ?? ?? ?? ? 3000? ?? ?11000? ?? ?29025? ?? ?10.34
? ?? ???30 JAMES? ?? ?? ?? ? 950? ?? ?11950? ?? ?29025? ?? ? 3.27
? ?? ???20 JONES? ?? ?? ?? ?2975? ?? ?14925? ?? ?29025? ?? ?10.25
? ?? ???10 KING? ?? ?? ?? ? 5000? ?? ?19925? ?? ?29025? ?? ?17.23
? ?? ???30 MARTIN? ?? ?? ???1250? ?? ?21175? ?? ?29025? ?? ? 4.31
? ?? ???10 MILLER? ?? ?? ???1300? ?? ?22475? ?? ?29025? ?? ? 4.48
? ?? ???20 SCOTT? ?? ?? ?? ?3000? ?? ?25475? ?? ?29025? ?? ?10.34
? ?? ???20 SMITH? ?? ?? ?? ? 800? ?? ?26275? ?? ?29025? ?? ? 2.76
? ?? ???30 TURNER? ?? ?? ???1500? ?? ?27775? ?? ?29025? ?? ? 5.17
? ?? ???30 WARD? ?? ?? ?? ? 1250? ?? ?29025? ?? ?29025? ?? ? 4.31

已选择14行。


3.使用子分区查出各部门薪水连续的总和。注意按部门分区。注意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)。

SQL> break on deptno skip 1??-- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal,
??2??sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和
??3??sum(sal) over (partition by deptno) 部门总和,??-- 部门统计的总和，同一部门总和不变
??4??100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",
??5??sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
??6??sum(sal) over () 总和,??-- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
??7??100*round(sal/sum(sal) over (),4) "总份额(%)"
??8??from emp
??9??/

DEPTNO ENAME? ? SAL 部门连续求和? ?部门总和 部门份额(%)? ?连续求和? ?总和??总份额(%)
------ ------ ----- ------------ ---------- ----------- ---------- ------ ----------
? ? 10 CLARK? ?2450? ?? ?? ?2450? ?? ? 8750? ?? ?? ? 28? ?? ? 2450??29025? ?? ? 8.44
? ?? ? KING? ? 5000? ?? ?? ?7450? ?? ? 8750? ?? ? 57.14? ?? ? 7450??29025? ?? ?17.23
? ?? ? MILLER??1300? ?? ?? ?8750? ?? ? 8750? ?? ? 14.86? ?? ? 8750??29025? ?? ? 4.48

? ? 20 ADAMS? ?1100? ?? ?? ?110