Oracle SQL-如何检索列的前5个值 [英] Oracle SQL - How to Retrieve highest 5 values of a column
问题描述
如何编写查询,其中仅返回具有最高或最低列值的选定行数.
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伪列的非解析解决方案.这很笨拙,因为在ORDER BY子句之前应用了ROWNUM,这可能会导致意外结果.几乎没有任何理由使用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个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!