Oracle rownum rank dense_rank

rownum rank dense_rank

直接看效果：

WITH Temp(id, opt)

AS (SELECT 1,1 FROM DUAL UNION ALL

    SELECT 1,1 FROM DUAL UNION ALL

    SELECT 2,1 FROM DUAL UNION ALL
    
    SELECT 3,1 FROM DUAL )

SELECT

    Temp.*,

    ROW_NUMBER() OVER(PARTITION BY opt ORDER BY ID) ROW_NUMBER,

    RANK() OVER(PARTITION BY opt ORDER BY ID) RANK,

    DENSE_RANK() OVER(PARTITION BY opt ORDER BY ID) DENSE_RANK

FROM Temp ;
           

結果：

row_number() 沒懸念，連續的1,2,3,4

rank是可重複不連續的，有間隙

dense_rank可重複且連續的，無間隙，dense英文的意思就是稠密地，按字面意思可以很容易分辨。