Oracle SQL - 如何检索列的最高 5 个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2306744/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL - How to Retrieve highest 5 values of a column
提问by Trevor
How do you write a query where only a select number of rows are returned with either the highest or lowest column value.
您如何编写一个查询，其中仅返回具有最高或最低列值的选定行数。
i.e. A report with the 5 highest salaried employees?
即工资最高的 5 名员工的报告？
回答by APC
The best way to do this is with analytic functions, RANK() or DENSE_RANK() ...
最好的方法是使用分析函数 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() compresses the gaps when there is a tie:
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>
Which behaviour you prefer depends upon your business requirements.
您喜欢哪种行为取决于您的业务需求。
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 valuesand the first five records sorted by high values
还有 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.
还有一个使用 ROWNUM 伪列的非解析解。这很笨拙，因为 ROWNUM 在 ORDER BY 子句之前应用，这可能会导致意外结果。很少有理由使用 ROWNUM 而不是 ROW_NUMBER() 或排名函数之一。
回答by road242
Try this one:
试试这个：
SELECT * FROM
(SELECT field1, field2 FROM fields order by field1 desc)
where rownum <= 5
Also take a look on this resourcefor a more detailed description on how rownum works.
另请查看此资源以获取有关 rownum 如何工作的更详细说明。
回答by OMG Ponies
Oracle 9i+ provides analytic functions:
Oracle 9i+ 提供了分析功能：
All require the use of the OVERclause, which allows PARTITION BYand ORDER BYclauses to properly tune the ROW_NUMBER/RANK/DENSE_RANKvalue returned.
所有需要使用的OVER条款，允许PARTITION BY和ORDER BY条款适当调整ROW_NUMBER/ RANK/DENSE_RANK返回值。
Prior to 9i, the only option was to work with ROWNUM- which incidentally is faster than using ROW_NUMBER(link).
在 9i 之前，唯一的选择是使用ROWNUM- 顺便说一下，这比使用ROW_NUMBER( link)快。
回答by Kaushik Nayak
In Oracle 12c, this can be achieved using FETCH..FIRSTROWS..ONLY
在Oracle 12c 中，这可以使用FETCH..FIRSTROWS..ONLY
To fetch the top 5 highest salaries.
获取工资最高的前 5 名。
SELECT *
FROM EMPLOYEES
ORDER BY SALARY DESC
FETCH FIRST 5 ROWS ONLY;
回答by Gowtham v
Select emp_id , salary from employees
Order by salary desc
Limit 5;
