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;


天聖經:神的心不僅在神的聖言當中,也在神所創造的萬物之中,天涯海角每一處都有神的心
Related Posts Plugin for WordPress, Blogger...