row_number rank dense_rank

注意工资为3000及2975.00的排序结果

SQL> select b.dname,
       a.ename,
       a.sal,
       row_number() over(partition by b.dname order by a.sal desc) as rownumber,
       rank() over(partition by b.dname order by a.sal desc) as rank,
       dense_rank() over(partition by b.dname order by a.sal desc) as denserank
  from emp a
 inner join dept b on b.deptno = a.deptno
 order by 1, 3 desc
 /

DNAME			     ENAME			 SAL  ROWNUMBER       RANK  DENSERANK
---------------------------- -------------------- ---------- ---------- ---------- ----------
ACCOUNTING		     KING			5000	      1 	 1	    1
ACCOUNTING		     CLARK			2450	      2 	 2	    2
ACCOUNTING		     MILLER			1300	      3 	 3	    3
RESEARCH		     FORD			3000	      1 	 1	    1
RESEARCH		     SCOTT			3000	      2 	 1	    1
RESEARCH		     JONES			2975	      3 	 3	    2
RESEARCH		     ADAMS			1100	      4 	 4	    3
RESEARCH		     SMITH			 800	      5 	 5	    4
SALES			     BLAKE			2850	      1 	 1	    1
SALES			     ALLEN			1600	      2 	 2	    2
SALES			     TURNER			1500	      3 	 3	    3
SALES			     WARD			1250	      4 	 4	    4
SALES			     MARTIN			1250	      5 	 4	    4
SALES			     JAMES			 950	      6 	 6	    5

14 rows selected.

SQL>