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

问题描述

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

How do you write a query where only a select number of rows are returned with either the highest or lowest column value.

即一份薪水最高的 5 名员工的报告?

i.e. A report with the 5 highest salaried employees?

推荐答案

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

The best way to do this is with analytic functions, RANK() or 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() 在平局时压缩间隙:

DENSE_RANK() compresses the gaps when there is a tie:

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>

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

Which behaviour you prefer depends upon your business requirements.

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

There is also the ROW_NUMBER() analytic function which we can use to return a precise number of rows. However, we should avoid using solutions based on row number unless the business logic is happy to arbitrarily truncate the result set in the event of a tie. There is a difference between asking for the five highest values and the first five records sorted by high values

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

There is also a non-analytic solution using the ROWNUM pseudo-column. This is clunky because ROWNUM is applied before the ORDER BY clause, which can lead to unexpected results. There is rarely any reason to use ROWNUM instead of ROW_NUMBER() or one of the ranking functions.

这篇关于Oracle SQL - 如何检索列的最高 5 个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！