ORACLE 查询第N条记录及RANK()内部排序

方式一：结果集相减

SELECT column1, column2 FROM (SELECT * FROM table_name ORDER BY order_column DESC )
WHERE ROWNUM<=2
MINUS 
SELECT column1, column2 FROM (SELECT * FROM table_name ORDER BY order_column DESC )
WHERE ROWNUM<=1

方式二：ROWNUM定位法

-- 查询第N条记录
SELECT * FROM (
                SELECT
                        T.*,
                        ROWNUM AS RN            
                FROM
                        table_name T           
                WHERE
                        query_column1 = '?'                      
                    AND query_column2  = '?'           
                ORDER BY
                        order_column DESC) TEMP
                
WHERE TEMP.RN = 'N'

方式三：子查询筛选

SELECT
        ID,
        NAME
FROM
        (
                SELECT
                        T.ID,
                        T.NAME    ,
                        RANK() OVER(ORDER BY order_column) AS RN
                FROM
                        TABLE_NAME T
        ) TMP
WHERE
        TMP.RN + 1 =
        (
                SELECT
                        MAX(RN)
                FROM
                        (
                                SELECT
                                        T.ID,
                                        T.NAME    ,
                                        RANK() OVER(ORDER BY order_column) AS RN
                                FROM
                                        TABLE_NAME T
                        )
        )

RANK()内部排序 及 PARTITION BY结果集分组

-- 查询每组前五名
SELECT
        *
FROM
        (
                SELECT
                        E.*,
                        RANK() OVER (PARTITION BY E.GROUP_COLUMN ORDER BY E.ORDER_COLUMN) RK
                FROM
                        TABLE_NAME E
        )
        TMP
WHERE
        TMP.RK <= 5;

rank()与dense_rank()区别：

相同点：1.都会根据order by 的内容进行递增排序。2.遇到partition by值相同时RK不会递增

不同点：1.在下一个相同值rank()会跳号，dense_rank()不会跳号。

              2.遇到下一个不同值时rank()会累计，dense_rank() 不会累计。

SELECT
        COLUMN1,
        COLUMN2,
        RANK() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2 DESC) RK,
        DENSE_RANK() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2 DESC) D_RK
FROM
        TABLE_NAME;

COLUMN1  |  COLUMN2  |  RK  |  D_RK  
-------------------------------------
  MON       11000       1        1   
  TUES      12000       2        2   
  WED       13000       3        3   
  THUR      15000       4        4   
  FRI       15000       4        4   
  SAT       18000       6        5