【正文】 derby_clause, window_clause)功能該函數(shù)用于對某一個結(jié)果集進(jìn)行分組，并按規(guī)則對組內(nèi)的數(shù)據(jù)進(jìn)行排序，最后生成組內(nèi)序列號。示例1SELECT ROW_NUMBER () OVER (PARTITION BY department_id ORDER BY employee_id) AS in_depart_id, department_id, last_name, employee_idFROM IN_DEPART_IDDEPARTMENT_IDLAST_NAMEEMPLOYEE_ID110Whalen200120Hartstein201220Fay202130Raphaely114230Khoo115330Baida116430Tobias117530Himuro118630Colmenares119140Mavris203150Weiss120250Fripp121350Kaufling122450Vollman123550Mourgos124650Nayer125從上面的例子中可以看到，ROW_NUMBER先是對部門進(jìn)行分組，然后在部門內(nèi)部按照員工號進(jìn)行排序，最后根據(jù)部門內(nèi)員工的數(shù)量生成序列號，該序列號只在一個部門內(nèi)有效。SUM() OVER()語法SUM(field) OVER(partition_clause, orderby_clause, window_clause)說明對結(jié)果集中的某一字段求和，被求和的每一個字段都是前面所有字段之和。該函數(shù)使用戶能夠看到字段的累積求和過程。這個函數(shù)的優(yōu)點表現(xiàn)在，查詢中可以包含任意數(shù)量的分析函數(shù)，而且每個分析函數(shù)都能夠有它自己的partition_clause子句和組。在做報表時，這個函數(shù)尤其有用。示例1下面的例子用了一個分析函數(shù)。其中窗口子句“RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”是默認(rèn)的，即使不寫它，Oracle也會采用。break on dname skip1 按照dname分段顯示，空1格SELECT dname, ename, sal, SUM(sal) OVER(PARTITION BY dname ORDER BY dname, ename) running_total FROM emp, dept WHERE = ORDER BY dname, ename/DNAME ENAME SAL RUNNING_TOTAL ACCOUNTING CLARK 2450 2450 KING 5000 7450 MILLER 1300 8750RESEARCH ADAMS 1100 1100 FORD 3000 4100 JONES 2975 7075 SCOTT 3000 10075 SMITH 800 10875SALES ALLEN 1600 1600 BLAKE 2850 4450 JAMES 950 5400 MARTIN 1250 6650 TURNER 1500 8150 WARD 1250 9400示例2下面這個例子使用了兩個分析函數(shù)，同時采用了默認(rèn)的窗口子句。break on dname skip1SELECT dname, ename, sal, SUM(sal) OVER(PARTITION BY dname ORDER BY dname, ename) dept_running_total, SUM(sal) OVER(ORDER BY dname, ename) running_total FROM emp, dept WHERE = ORDER BY dname, ename/DNAME ENAME SAL DEPT_RUNNING_TOTAL RUNNING_TOTAL ACCOUNTING CLARK 2450 2450 2450 KING 5000 7450 7450 MILLER 1300 8750 8750RESEARCH ADAMS 1100 1100 9850 FORD 3000 4100 12850 JONES 2975 7075 15825 SCOTT 3000 10075 18825 SMITH 800 10875 19625SALES ALLEN 1600 1600 21225 BLAKE 2850 4450 24075 JAMES 950 5400 25025 MARTIN 1250 6650 26275 TURNER 1500 8150 27775 WARD 1250 9400 29025LEAD() OVER()語法LEAD(field, next_number, default_value)OVER(partition_clause, orderby_clause, window_clause)說明LEAD() OVER()函數(shù)先查詢一組值，然后獲取當(dāng)前記錄之后的第N個記錄。Field是一個字段或表達(dá)式，它為搜索提供依據(jù)。Next_number是一個數(shù)字，它表示向前推進(jìn)多少行。Default_value表示當(dāng)沒有找到相關(guān)記錄時將采用的默認(rèn)值。如果為LEAD() OVER()提供的next_number為負(fù)數(shù)，則它與LAG() OVER()完全相同。示例1SELECT ename, sal, LEAD(ename, 1, 39。N/A39。) OVER(ORDER BY sal) next_ename, LEAD(sal, 1, null) OVER(ORDER BY sal) next_sal, LEAD(ename, 2, 39。N/A39。) OVER(ORDER BY sal) next_ename, LEAD(sal, 2, null) OVER(ORDER BY sal) next_sal FROM emp ORDER BY sal/ENAME SAL NEXT_ENAME NEXT_SAL NEXT_ENAME NEXT_SAL SMITH 800 JAMES 950 ADAMS 1100JAMES 950 ADAMS 1100 WARD 1250ADAMS 1100 WARD 1250 MARTIN 1250WARD 1250 MARTIN 1250 MILLER 1300MARTIN 1250 MILLER 1300 TURNER 1500MILLER 1300 TURNER 1500 ALLEN 1600TURNER 1500 ALLEN 1600 CLARK 2450ALLEN 1600 CLARK 2450 BLAKE 2850CLARK 2450 BLAKE 2850 JONES 2975BLAKE 2850 JONES 2975 SCOTT 3000JONES 2975 SCOTT 3000 FORD 3000SCOTT 3000 FORD 3000 KING 5000FORD 3000 KING 5000 N/AKING 5000 N/A N/A示例2在LEAD() OVER()函數(shù)中同樣可以使用PARTITION_CLAUSE。break on dname skip 1SELECT dname, ename, sal, LEAD(ename, 1, 39。N/A39。) OVER(PARTITION BY dname ORDER BY dname, sal) next_ename, LEAD(sal, 1, null) OVER(PARTITION BY dname ORDER BY dname, sal) next_sal FROM emp, dept WHERE = ORDER BY dname, sal/DNAME ENAME SAL NEXT_ENAME NEXT_SAL ACCOUNTING MILLER 1300 CLARK 2450 CLARK 2450 KING 5000 KING 5000 N/A RESEARCH SMITH 800 ADAMS 1100 ADAMS 1100 JONES 2975 JONES 2975 SCOTT 3000 SCOTT 3000 FORD 3000 FORD 3000 N/A SALES JAMES 950 MARTIN 1250 MARTIN 1250 WARD 1250 WARD 1250 TURNER 1500 TURNER 1500 ALLEN 1600 ALLEN 1600 BLAKE 2850 BLAKE 2850 N/A LAG() OVER()語法LAG(field, next_number, default_value)OVER(partition_clause, orderby_clause, window_clause)說明LAG() OVER()函數(shù)先查詢一組值，然后獲取當(dāng)前記錄之前的第N個記錄。Field是一個