Oracle rownum / row_number / rank / dense_rank 這四個的差異

Oracle PL/SQL 有 rownum 這個大家常用的內定關鍵字,

但在排序使用上, 卻有一點不方便,

這是因為 rownum 會在排序前先配置好的緣故,

這時候, 用 oracle 分析函數 row_number() 是一個不錯的選擇,

範例如下 :

 程式碼

-- 建立 Temp Table
CREATE TABLE TOM1(
  EMPLOYEE VARCHAR2(30)
, DEPT_NO  VARCHAR2(30)
, SALARY   NUMBER
);
 
-- 建立 Temp Data
INSERT INTO TOM1 VALUES( 'B', '電腦室', 1000 );
INSERT INTO TOM1 VALUES( 'C', '會計室', 2000 );
INSERT INTO TOM1 VALUES( 'D', '電腦室', 2500 );
INSERT INTO TOM1 VALUES( 'A', '總務室', 2500 );
INSERT INTO TOM1 VALUES( 'E', '會計室', 4000 );
INSERT INTO TOM1 VALUES( 'F', '會計室', 2500 );
INSERT INTO TOM1 VALUES( 'I', '總務室', 2500 );
INSERT INTO TOM1 VALUES( 'H', '電腦室', 5000 );
INSERT INTO TOM1 VALUES( 'G', '總務室', 3000 );
COMMIT;
 
-- 範例 1: 按照薪水高低排序, 但這時候的 ROWNUM 卻不是 1, 2, 3, ... 一一顯示
SELECT ROWNUM
     , EMPLOYEE
     , DEPT_NO
     , SALARY
  FROM TOM1
 ORDER BY SALARY DESC;

 
-- 範例 2: 先利用 Subquery 將薪水按照高低排序, 再抓取 ROWNUM, 這時候 ROWNUM 是 1, 2, 3, ... 一一顯示
SELECT ROWNUM
     , A.*
  FROM (SELECT EMPLOYEE
             , DEPT_NO
             , SALARY
          FROM TOM1
         ORDER BY SALARY DESC
       ) A;

 
-- 範例 3: 不用 Subquery 而用分析函數, 將薪水按照高低排序, 且 ROW NUM 是 1, 2, 3, ... 一一顯示
SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) ROW_NUM
     , EMPLOYEE
     , DEPT_NO
     , SALARY
  FROM TOM1;

 
-- 範例 4: ROW_NUMBER / RANK / DENSE_RANK 三者的差異
SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) ROW_NUM
     , RANK()       OVER(ORDER BY SALARY DESC) RANK
     , DENSE_RANK() OVER(ORDER BY SALARY DESC) DENSE_RANK
     , EMPLOYEE
     , DEPT_NO
     , SALARY
  FROM TOM1;

 
-- 範例 5: ROW_NUMBER / RANK / DENSE_RANK 三者加上 PARTITION (用什麼來區別重新算) 的差異
SELECT ROW_NUMBER() OVER(PARTITION BY DEPT_NO ORDER BY SALARY DESC) ROW_NUM
     , RANK()       OVER(PARTITION BY DEPT_NO ORDER BY SALARY DESC) RANK
     , DENSE_RANK() OVER(PARTITION BY DEPT_NO ORDER BY SALARY DESC) DENSE_RANK
     , EMPLOYEE
     , DEPT_NO
     , SALARY
  FROM TOM1;

昭佑.天翔