Oracle PL/SQL: Rank 排名次查詢

在 Oracle PL/SQL 中, 要如何做 "Rank 排名次" 查詢呢 ?

方式如下 :

 全部排名語法

SELECT ...
     , Rank( ) Over( Order by column1 ) as alias
  FROM ...

 分類排名語法

SELECT ...
     , Rank( ) Over( partition by column1 Order by column2 ) as alias
  FROM ...

 範例

-- 建立 Temp Table
CREATE TABLE TOM_TEST (
  EMPLOYEE_ID NUMBER
, DEPT_ID     NUMBER
, MONEY       NUMBER
);

-- 新增 Temp Data
INSERT INTO TOM_TEST VALUES( 1, 100, 10000 );
INSERT INTO TOM_TEST VALUES( 2, 100, 15000 );
INSERT INTO TOM_TEST VALUES( 3, 100, 20000 );
INSERT INTO TOM_TEST VALUES( 4, 200, 32000 );
INSERT INTO TOM_TEST VALUES( 5, 200, 40000 );
INSERT INTO TOM_TEST VALUES( 6, 200, 35000 );
INSERT INTO TOM_TEST VALUES( 7, 300, 30000 );
INSERT INTO TOM_TEST VALUES( 8, 300, 25000 );
INSERT INTO TOM_TEST VALUES( 9, 300, 10000 );
COMMIT;

-- 按 MONEY, 進行全部排名
select EMPLOYEE_ID
     , DEPT_ID
     , MONEY
     , rank() over( order by MONEY desc ) as RANK
  from TOM_TEST;


-- 以 DEPT_ID 分類, 按 MONEY, 進行分類排名
select EMPLOYEE_ID
     , DEPT_ID
     , MONEY
     , rank() over( partition by DEPT_ID order by MONEY desc ) as RANK
  from TOM_TEST;


-- 刪除 Temp Table
DROP TABLE TOM_TEST;

希望對各位在查詢排名上, 有所幫助.