Oracle中快速取得TOP值的方法（oracle中取top值）

Oracle中快速取得TOP值的方法

在实际的数据库应用中，经常需要查找一个表中某些字段值的TOP N值，比如某种产品销售额排名前10的省份、某个时间段内最热门的文章等。在Oracle中，可以使用多种方法来实现这个功能，下面就为大家介绍一些常用的方法及其优缺点。

一、使用子查询+ROWNUM

这是最基本的方法，即先查询出所有符合条件的记录，然后对结果排序，并且使用ROWNUM限制结果集的数量。具体代码如下：

SELECT *
FROM (
       SELECT product, sum(amount) total
       FROM sales
       GROUP BY product
       ORDER BY total DESC
     ) 
WHERE ROWNUM 

优点：实现简单，适用于小数据量和无索引情况。在Oracle 12c以上版本中，ROWNUM被优化后性能也有所提升。

缺点：查询整个表并排序，性能较差。如果需要查询TOP N值的字段没有索引，查询时间会更长。

二、使用子查询+ROW_NUMBER() OVER()

这种方法适用于Oracle 9i及以上版本，使用了SQL标准中的ROW_NUMBER() OVER()函数。这个函数可以给查询结果中的每一行添加一个行号，然后再根据这个行号筛选结果集。具体代码如下：

SELECT *
FROM (
       SELECT product, sum(amount) total, ROW_NUMBER() OVER (ORDER BY sum(amount) DESC) rn
       FROM sales
       GROUP BY product
     ) 
WHERE rn 

优点：相对于使用ROWNUM，这种方法不需要先对数据排序，所以适用于大数据量和索引建立顺序与查询顺序不一致的情况。

缺点：语法稍复杂，不如ROWNUM容易理解。

三、使用WITH子句+ROW_NUMBER() OVER()

WITH子句可以将一个复杂的查询分为多个易读易写的子查询，然后在最后的SELECT语句中使用这些子查询。结合ROW_NUMBER() OVER()函数，可以快速地得到TOP N值的结果。具体代码如下：

WITH tmp AS (
              SELECT product, sum(amount) total, ROW_NUMBER() OVER (ORDER BY sum(amount) DESC) rn
              FROM sales
              GROUP BY product
            )
SELECT * FROM tmp
WHERE rn 

优点：相比于使用子查询，WITH子句更易读、易维护。使用ROW_NUMBER() OVER()函数可以获得更好的性能。

缺点：在性能上略逊于使用子查询+ROW_NUMBER() OVER()的方法，因为多了一层子查询。

四、使用分析函数rank或dense_rank

分析函数是一种高级窗口函数，可以对满足条件的行进行排序并返回一个排名。其中rank函数和dense_rank函数可以用来查询TOP N值。具体代码如下：

SELECT product, total 
FROM (
       SELECT product, sum(amount) total, RANK() OVER (ORDER BY sum(amount) DESC) rnk
       FROM sales
       GROUP BY product
     ) 
WHERE rnk 

或

SELECT product, total 
FROM (
       SELECT product, sum(amount) total, DENSE_RANK() OVER (ORDER BY sum(amount) DESC) rnk
       FROM sales
       GROUP BY product
     ) 
WHERE rnk 

优点：可以同时查询TOP N值和排名。

缺点：语法较为复杂，并且在数据量较大时性能也不如ROW_NUMBER() OVER()。

综上所述，不同的查询场景需要使用不同的方法来得到TOP N值。如果数据量较小，可以使用第一种方法；如果数据量很大并且需要对关键字建立索引，可以使用第二种、三种方法；如果还需要查询排名，则可以使用第四种方法。同时，如果要得到最好的性能，需要对表的结构和索引进行优化。