今天在javaeye上看到一道面試題，很多人都用over函數解決的
特意查了一下它的用法
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> 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> 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 ?? ?? 1100 ?? 10875 ?? 10.11 ?? 9850?? 29025 ?? 3.79
?? FORD 3000 ?? ?? 4100 ?? 10875 ?? 27.59 ?? 12850?? 29025 ?? 10.34
?? JONES 2975 ?? ?? 7075 ?? 10875 ?? 27.36 ?? 15825?? 29025 ?? 10.25
?? SCOTT 3000 ?? ??? 10075 ?? 10875 ?? 27.59 ?? 18825?? 29025 ?? 10.34
?? SMITH 800 ?? ??? 10875 ?? 10875 ?? ??? 7.36 ?? 19625?? 29025 ?? 2.76

30 ALLEN 1600 ?? ?? 1600 ?? 9400 ?? 17.02 ?? 21225?? 29025 ?? 5.51
?? BLAKE 2850 ?? ?? 4450 ?? 9400 ?? 30.32 ?? 24075?? 29025 ?? 9.82
?? JAMES 950 ?? ?? 5400 ?? 9400 ?? 10.11 ?? 25025?? 29025 ?? 3.27
?? MARTIN?? 1250 ?? ?? 6650 ?? 9400 ?? ??? 13.3 ?? 26275?? 29025 ?? 4.31
?? TURNER?? 1500 ?? ?? 8150 ?? 9400 ?? 15.96 ?? 27775?? 29025 ?? 5.17
?? WARD 1250 ?? ?? 9400 ?? 9400 ?? ??? 13.3 ?? 29025?? 29025 ?? 4.31


已選擇14行。



4.來一個綜合的例子，求和規則有按部門分區的，有不分區的例子
SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
?? 2?? sum(sal) over (order by deptno,sal) sum
?? 3?? from emp;

DEPTNO ENAME ?? ?? ?? SAL DEPT_SUM ?? ??? SUM
---------- ---------- ---------- ---------- ----------
?? ??? 10 MILLER ?? ?? ??? 1300 ?? 1300 ?? 1300
?? ?? ??? CLARK ?? ?? ?? 2450 ?? 3750 ?? 3750
?? ?? ??? KING ?? ?? ?? 5000 ?? 8750 ?? 8750

?? ??? 20 SMITH ?? ?? ?? 800 ?? ??? 800 ?? 9550
?? ?? ??? ADAMS ?? ?? ?? 1100 ?? 1900 ?? 10650
?? ?? ??? JONES ?? ?? ?? 2975 ?? 4875 ?? 13625
?? ?? ??? SCOTT ?? ?? ?? 3000 ?? 10875 ?? 19625
?? ?? ??? FORD ?? ?? ?? 3000 ?? 10875 ?? 19625

?? ??? 30 JAMES ?? ?? ?? 950 ?? ??? 950 ?? 20575
?? ?? ??? WARD ?? ?? ?? 1250 ?? 3450 ?? 23075
?? ?? ??? MARTIN ?? ?? ??? 1250 ?? 3450 ?? 23075
?? ?? ??? TURNER ?? ?? ??? 1500 ?? 4950 ?? 24575
?? ?? ??? ALLEN ?? ?? ?? 1600 ?? 6550 ?? 26175
?? ?? ??? BLAKE ?? ?? ?? 2850 ?? 9400 ?? 29025


已選擇14行。



5.來一個逆序的，即部門從大到小排列，部門里各員工的薪水從高到低排列，累計和的規則不變。

SQL> select deptno,ename,sal,
?? 2?? sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,
?? 3?? sum(sal) over (order by deptno desc,sal desc) sum
?? 4?? from emp;

DEPTNO ENAME ?? ?? ?? SAL DEPT_SUM ?? ??? SUM
---------- ---------- ---------- ---------- ----------
?? ??? 30 BLAKE ?? ?? ?? 2850 ?? 2850 ?? 2850
?? ?? ??? ALLEN ?? ?? ?? 1600 ?? 4450 ?? 4450
?? ?? ??? TURNER ?? ?? ??? 1500 ?? 5950 ?? 5950
?? ?? ??? WARD ?? ?? ?? 1250 ?? 8450 ?? 8450
?? ?? ??? MARTIN ?? ?? ??? 1250 ?? 8450 ?? 8450
?? ?? ??? JAMES ?? ?? ?? 950 ?? 9400 ?? 9400

?? ??? 20 SCOTT ?? ?? ?? 3000 ?? 6000 ?? 15400
?? ?? ??? FORD ?? ?? ?? 3000 ?? 6000 ?? 15400
?? ?? ??? JONES ?? ?? ?? 2975 ?? 8975 ?? 18375
?? ?? ??? ADAMS ?? ?? ?? 1100 ?? 10075 ?? 19475
?? ?? ??? SMITH ?? ?? ?? 800 ?? 10875 ?? 20275

?? ??? 10 KING ?? ?? ?? 5000 ?? 5000 ?? 25275
?? ?? ??? CLARK ?? ?? ?? 2450 ?? 7450 ?? 27725
?? ?? ??? MILLER ?? ?? ??? 1300 ?? 8750 ?? 29025


已選擇14行。



6.體會：在"... from emp;"后面不要加order?? by 子句，使用的分析函數的(partition by deptno order by sal)
里已經有排序的語句了，如果再在句尾添加排序子句，一致倒罷了，不一致，結果就令人費勁了。如：

SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
?? 2?? sum(sal) over (order by deptno,sal) sum
?? 3?? from emp
?? 4?? order by deptno desc;

DEPTNO ENAME ?? ?? ?? SAL DEPT_SUM ?? ??? SUM
---------- ---------- ---------- ---------- ----------
?? ??? 30 JAMES ?? ?? ?? 950 ?? ??? 950 ?? 20575
?? ?? ??? WARD ?? ?? ?? 1250 ?? 3450 ?? 23075
?? ?? ??? MARTIN ?? ?? ??? 1250 ?? 3450 ?? 23075
?? ?? ??? TURNER ?? ?? ??? 1500 ?? 4950 ?? 24575
?? ?? ??? ALLEN ?? ?? ?? 1600 ?? 6550 ?? 26175
?? ?? ??? BLAKE ?? ?? ?? 2850 ?? 9400 ?? 29025

?? ??? 20 SMITH ?? ?? ?? 800 ?? ??? 800 ?? 9550
?? ?? ??? ADAMS ?? ?? ?? 1100 ?? 1900 ?? 10650
?? ?? ??? JONES ?? ?? ?? 2975 ?? 4875 ?? 13625
?? ?? ??? SCOTT ?? ?? ?? 3000 ?? 10875 ?? 19625
?? ?? ??? FORD ?? ?? ?? 3000 ?? 10875 ?? 19625

?? ??? 10 MILLER ?? ?? ??? 1300 ?? 1300 ?? 1300
?? ?? ??? CLARK ?? ?? ?? 2450 ?? 3750 ?? 3750
?? ?? ??? KING ?? ?? ?? 5000 ?? 8750 ?? 8750


已選擇14行