ROW_NUMBER()OVER() 是OracleSQL分析函数，主要是用来对要查询的数据分组排序使用。
使用方法
ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col2）
对列col1分组，col2排序操作。
例子：

SQL>SELECT2ROW_NUMBER()OVER(PARTITIONBYdeptnoORDERBYsal,empno)ASrn,3empno,ename,sal,deptno4FROMemp;RNEMPNOENAMESALDEPTNO--------------------------------------------------17934MILLER13001027782CLARK24501037839KING50001017369SMITH8002027876ADAMS11002037566JONES29752047788SCOTT30002057902FORD30002017900JAMES9503027521WARD12503037654MARTIN125030RNEMPNOENAMESALDEPTNO--------------------------------------------------47844TURNER15003057499ALLEN16003067698BLAKE28503014rowsselected.

同时也可以单独使用其来对结果进行排序
可以和order by 对比一下：

SQL>SELECTempno,ename,sal,2ROW_NUMBER()OVER(ORDERBYsal,empno)ASrn3FROMemp;EMPNOENAMESALRN----------------------------------------7369SMITH80017900JAMES95027876ADAMS110037521WARD125047654MARTIN125057934MILLER130067844TURNER150077499ALLEN160087782CLARK245097698BLAKE2850107566JONES297511EMPNOENAMESALRN----------------------------------------7788SCOTT3000127902FORD3000137839KING50001414rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:3145491563---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||14|462|4(25)|00:00:01||1|WINDOWSORT||14|462|4(25)|00:00:01||2|TABLEACCESSFULL|EMP|14|462|3(0)|00:00:01|---------------------------------------------------------------------------Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------5recursivecalls0dbblockgets16consistentgets1physicalreads0redosize1049bytessentviaSQL*Nettoclient523bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)14rowsprocessedSQL>SELECTempno,ename,sal2FROMemp3ORDERBYsal,empno;EMPNOENAMESAL------------------------------7369SMITH8007900JAMES9507876ADAMS11007521WARD12507654MARTIN12507934MILLER13007844TURNER15007499ALLEN16007782CLARK24507698BLAKE28507566JONES2975EMPNOENAMESAL------------------------------7788SCOTT30007902FORD30007839KING500014rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:150391907---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||14|462|4(25)|00:00:01||1|SORTORDERBY||14|462|4(25)|00:00:01||2|TABLEACCESSFULL|EMP|14|462|3(0)|00:00:01|---------------------------------------------------------------------------Note------dynamicsamplingusedforthisstatement(level=2)Statistics----------------------------------------------------------4recursivecalls0dbblockgets16consistentgets1physicalreads0redosize943bytessentviaSQL*Nettoclient523bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)14rowsprocessed

对比ORDER BY 子句，排序结果一样，使用ROW_NUMBER()OVER()函数可生产RN列，便于在某些列表程序选择行数。