資源描述：
《ORACLE中OVER函數(shù)的用法》由會員上傳分享，免費在線閱讀，更多相關內容在教育資源-天天文庫。
1、oracleover函數(shù)詳解今天在javaeye上看到一道面試題，很多人都用over函數(shù)解決的特意查了一下它的用法SQL>selectdeptno,ename,sal??2??fromemp??3??orderbydeptno;DEPTNOENAME??????SAL------------------------------?????10CLARK??????2450???????KING??????5000???????MILLER???????1300?????20SMITH??????800???????ADAMS??????1
2、100???????FORD??????3000???????SCOTT??????3000???????JONES??????2975?????30ALLEN??????1600???????BLAKE??????2850???????MARTIN???????1250???????JAMES??????950???????TURNER???????1500???????WARD??????1250已選擇14行。2.先來一個簡單的，注意over(...)條件的不同，使用sum(sal)over(orderbyename)...查詢員工
3、的薪水“連續(xù)”求和,注意over(order??byename)如果沒有orderby子句，求和就不是“連續(xù)”的，放在一起，體會一下不同之處：SQL>selectdeptno,ename,sal,??2??sum(sal)over(orderbyename)連續(xù)求和,??3??sum(sal)over()總和,??????????--此處sum(sal)over()等同于sum(sal)??4??100*round(sal/sum(sal)over(),4)"份額(%)"??5??fromemp??6??/DEPTNOENAME????
4、??SAL連續(xù)求和??總和oracleover函數(shù)詳解今天在javaeye上看到一道面試題，很多人都用over函數(shù)解決的特意查了一下它的用法SQL>selectdeptno,ename,sal??2??fromemp??3??orderbydeptno;DEPTNOENAME??????SAL------------------------------?????10CLARK??????2450???????KING??????5000???????MILLER???????1300?????20SMITH??????800??????
5、?ADAMS??????1100???????FORD??????3000???????SCOTT??????3000???????JONES??????2975?????30ALLEN??????1600???????BLAKE??????2850???????MARTIN???????1250???????JAMES??????950???????TURNER???????1500???????WARD??????1250已選擇14行。2.先來一個簡單的，注意over(...)條件的不同，使用sum(sal)over(orderby
6、ename)...查詢員工的薪水“連續(xù)”求和,注意over(order??byename)如果沒有orderby子句，求和就不是“連續(xù)”的，放在一起，體會一下不同之處：SQL>selectdeptno,ename,sal,??2??sum(sal)over(orderbyename)連續(xù)求和,??3??sum(sal)over()總和,??????????--此處sum(sal)over()等同于sum(sal)??4??100*round(sal/sum(sal)over(),4)"份額(%)"??5??fromemp??6??/DE
7、PTNOENAME??????SAL連續(xù)求和??總和份額(%)------------------------------------------------------------?????20ADAMS??????1100??1100??29025??3.79?????30ALLEN??????1600??2700??29025??5.51?????30BLAKE??????2850??5550??29025??9.82?????10CLARK??????2450??8000??29025??8.44?????20FORD?????
8、?3000??11000??29025??10.34?????30JAMES??????950??11950??29025??3.27?????20JONES??????2975??14925??29025
