row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根據COL1分組,在分組内部根據 COL2排序,而此函數計算的值就表示每組内部排序後的順序編号(組内連續的唯一的)
與rownum的差別在于:使用rownum進行排序的時候是先對結果集加入僞列rownum然後再進行排序,而此函數在包含排序從句後是先排序再計算行号碼.
row_number()和rownum差不多,功能更強一點(可以在各個分組内從1開時排序).
rank()是跳躍排序,有兩個第二名時接下來就是第四名(同樣是在各個分組内).
dense_rank()l是連續排序,有兩個第二名時仍然跟着第三名。相比之下row_number是沒有重複值的
查詢員工的工資,按部門排序
select ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order from scott.emp;
執行結果:
ENAME SAL SAL_ORDER
-------------------- ---------- ----------
KING 5000 1
CLARK 2450 2
MILLER 1300 3
SCOTT 3000 1
FORD 3000 2
JONES 2975 3
ADAMS 1100 4
SMITH 800 5
BLAKE 2850 1
ALLEN 1600 2
TURNER 1500 3
WARD 1250 4
MARTIN 1250 5
JAMES 950 6
已選擇14行
查詢每個部門的最高工資
select deptno,ename,sal from
(select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order
from scott.emp) where sal_order <2;
執行結果:
DEPTNO ENAME SAL
---------- -------------------- ----------
10 KING 5000
20 SCOTT 3000
30 BLAKE 2850