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英文的意思就是稠密地，按字面意思可以很容易分辨。