隨筆 - 59  文章 - 70  trackbacks - 0
          <2009年3月>
          22232425262728
          1234567
          891011121314
          15161718192021
          22232425262728
          2930311234

          常用鏈接

          留言簿(5)

          隨筆分類

          隨筆檔案

          搜索

          •  

          積分與排名

          • 積分 - 174131
          • 排名 - 340

          最新評(píng)論

          閱讀排行榜

          評(píng)論排行榜

          今天在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

          FeedBack:
          # re: oracle over函數(shù)詳解 2009-03-30 10:07 shiyiwan
          第5個(gè)結(jié)果有點(diǎn)費(fèi)解哦  回復(fù)  更多評(píng)論
            
          主站蜘蛛池模板: 安塞县| 额尔古纳市| 兴宁市| 资中县| 武鸣县| 中西区| 五寨县| 策勒县| 南汇区| 兴文县| 彰化市| 油尖旺区| 禄丰县| 鸡泽县| 安丘市| 武平县| 修水县| 湘潭县| 贵溪市| 龙井市| 巢湖市| 朔州市| 明星| 同仁县| 韶山市| 五常市| 开江县| 南丰县| 楚雄市| 确山县| 北川| 万宁市| 吉安县| 察哈| 揭阳市| 大理市| 东丽区| 宾川县| 门源| 军事| 利津县|