Oracle SQL -如何检索列的最高5个值

Question

如何编写一个查询，其中只返回具有最高列值或最低列值的选定行数。

例如，一份包含5个最高收入员工的报告？

Answer 1

最好的方法是使用分析函数，RANK()或DENSE_RANK()……

SQL> select * from (
  2        select empno
  3               , sal
  4               , rank() over (order by sal desc) as rnk
  5        from emp)
  6  where rnk <= 5
  7  /

     EMPNO        SAL        RNK
---------- ---------- ----------
      7839       5000          1
      7788       3000          2
      7902       3000          2
      7566       2975          4
      8083       2850          5
      7698       2850          5

6 rows selected.

SQL>

当存在平局时，DENSE_RANK()会压缩间隙：

SQL> select * from (
  2        select empno
  3               , sal
  4               , dense_rank() over (order by sal desc) as rnk
  5        from emp)
  6  where rnk <= 5
  7  /

     EMPNO        SAL        RNK
---------- ---------- ----------
      7839       5000          1
      7788       3000          2
      7902       3000          2
      7566       2975          3
      8083       2850          4
      7698       2850          4
      8070       2500          5

7 rows selected.

SQL>

您更喜欢哪种行为取决于您的业务需求。

还有ROW_NUMBER()分析函数，我们可以使用它返回精确的行数。但是，我们应该避免使用基于行号的解决方案，除非业务逻辑乐于在平局中任意截断结果集。请求五个最高值与按高值排序的前五个记录之间存在差异

还有一个使用ROWNUM伪列的非解析解决方案。这很笨拙，因为ROWNUM是在ORDER BY子句之前应用的，这可能会导致意外的结果。很少有任何理由使用ROWNUM而不是ROW_NUMBER()或其中一个排名函数。

Answer 2

试试这个：

SELECT * FROM 
    (SELECT field1, field2 FROM fields order by field1 desc) 
where rownum <= 5

也可以在this resource上查看有关rownum如何工作的更详细的描述。

Answer 3

Oracle 9i+提供分析函数：

• ROW_NUMBER
• RANK
• DENSE_RANK

所有这些都需要使用OVER子句，该子句允许PARTITION BY和ORDER BY子句正确地调优返回的ROW_NUMBER/RANK/DENSE_RANK值。

在9i之前，唯一的选择是使用ROWNUM -顺便说一句，它比使用ROW_NUMBER (link)更快。