今天在javaeye上看到一道面試題，很多人都用over函數(shù)解決的
特意查了一下它的用法
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.先來(lái)一個(gè)簡(jiǎn)單的，注意over(...)條件的不同，
使用 sum(sal) over (order by ename)... 查詢員工的薪水“連續(xù)”求和,
注意over (order?? by ename)如果沒(méi)有order by 子句，求和就不是“連續(xù)”的，
放在一起，體會(huì)一下不同之處：
SQL> select deptno,ename,sal,
?? 2?? sum(sal) over (order by ename) 連續(xù)求和,
?? 3?? sum(sal) over () 總和, ?? ?? ?? ?? ?? -- 此處sum(sal) over () 等同于sum(sal)
?? 4?? 100*round(sal/sum(sal) over (),4) "份額(%)"
?? 5?? from emp
?? 6?? /
DEPTNO ENAME ?? ?? ?? SAL 連續(xù)求和 ?? 總和 份額(%)
---------- ---------- ---------- ---------- ---------- ----------
?? ??? 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.使用子分區(qū)查出各部門薪水連續(xù)的總和。注意按部門分區(qū)。注意over(...)條件的不同，
sum(sal) over (partition by deptno order by ename) 按部門“連續(xù)”求總和
sum(sal) over (partition by deptno) 按部門求總和
sum(sal) over (order by deptno，ename) 不按部門“連續(xù)”求總和
sum(sal) over () 不按部門，求所有員工總和，效果等同于sum(sal)。
SQL> select deptno,ename,sal,
?? 2?? sum(sal) over (partition by deptno order by ename) 部門連續(xù)求和,--各部門的薪水"連續(xù)"求和
?? 3?? sum(sal) over (partition by deptno) 部門總和,?? -- 部門統(tǒng)計(jì)的總和，同一部門總和不變
?? 4?? 100*round(sal/sum(sal) over (partition by deptno),4) "部門份額(%)",
?? 5?? sum(sal) over (order by deptno,ename) 連續(xù)求和, --所有部門的薪水"連續(xù)"求和
?? 6?? sum(sal) over () 總和,?? -- 此處sum(sal) over () 等同于sum(sal)，所有員工的薪水總和
?? 7?? 100*round(sal/sum(sal) over (),4) "總份額(%)"
?? 8?? from emp
?? 9?? /
DEPTNO ENAME SAL 部門連續(xù)求和 部門總和 部門份額(%) 連續(xù)求和 總和?? 總份額(%)
------ ------ ----- ------------ ---------- ----------- ---------- ------ ----------
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.來(lái)一個(gè)綜合的例子，求和規(guī)則有按部門分區(qū)的，有不分區(qū)的例子
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.來(lái)一個(gè)逆序的，即部門從大到小排列，部門里各員工的薪水從高到低排列，累計(jì)和的規(guī)則不變。
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.體會(huì)：在"... from emp;"后面不要加order?? by 子句，使用的分析函數(shù)的(partition by deptno order by sal)
里已經(jīng)有排序的語(yǔ)句了，如果再在句尾添加排序子句，一致倒罷了，不一致，結(jié)果就令人費(fèi)勁了。如：
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行
posted on 2009-03-17 17:35
JasonChou 閱讀(6370)
評(píng)論(1) 編輯 收藏 所屬分類:
database