M1:使用ORACLE伪列ROWNUM
SELECT ROWNUM RANK,ord_col,... FROM (SELECT ord_col,... FROM tab ORDER BY ord_col DESC) WHEREROWNUM < N; |
这里使用subquery是因为ROWNUM在ORDER BY前求值。而对于TOP-N的一个变形,求顺序在M和N之间(M
M1(1): SELECT * FROM (SELECT ROWNUM RANK,ord_col,... FROM (SELECT ord_col,... FROM tab ORDER BY ord_col DESC) WHEREROWNUM < N) WHERE RANK > M; |
M1(2): (SELECT ROWNUM RANK,ord_col,... FROM (SELECT ord_col,... FROM tab ORDER BY ord_col DESC) WHEREROWNUM < N) MINUS (SELECT * FROM (SELECT ROWNUM RANK,ord_col,... FROM tab ORDER BY ord_col) WHERE ord_col |
M2:使用ORACLE RANK()和DENSE_RANK()函数:
RANK ( ) OVER ( [query_partition_clause] order_by_clause ) |
DENSE_RANK(expr[,expr]...) WITHIN GROUP (ORDER BY expr [DESC | ASC] [NULLS {FIRST | LAST}] [,expr [DESC | ASC] [NULLS {FIRST | LAST}]]...) |
SELECT RANK() OVER (ORDER BY ord_col DESC) rank FROM tab WHERE ROWNUM < N; |
M2几种变体:
简单变体M2(1): SELECT * FROM (SELECT RANK() OVER (ORDER BY ord_col DESC) rank FROM tab) WHERErank < N; |
M2(2):M-N SELECT * FROM (SELECT RANK() OVER (ORDER BY ord_col DESC) rank FROM tab WHERE rownum < N) WHERErank> M; 或 SELECT * FROM (SELECT RANK() OVER (ORDER BY ord_col DESC) rank FROM tab ) WHERErankBETWEEN NAND M; |
M2(3):PARTITION BY提供求分组TOP-N功能 SELECT * FROM (SELECT RANK() OVER (PARTITION BY group_col ORDER BY ord_col DESC) rank FROM tab ) WHERErank< N; |
M2(4):DENSE_RANK紧密排列,比如:1 1 2 3 3 4而不是RANK的:1 1 3 4 4 6 SELECT * FROM (SELECT DENSE_RANK() OVER (ORDER BY ord_col DESC) rank FROM tab) WHERErank < N; |
M2(5):求TOP N%问题 SELECT *FROM (SELECT ord_col,RANK() OVER (ORDER BYord_col DESC) rank FROM tab) WHERErank < N%*(SELECT count(1)FROM tab) |