ORACLE中的几种TOP-N查询方法

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)

这些求 TOP-N的方法中，应该有一些效率的差别，一般来说用M2效率要较M1高一些,各个变体方法的效率应该差别不大，具体问题具体分析，也许还有更好的求 TOP-N的查询方法，欢迎交流。