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

Question

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

例如，一份薪水最高的5位员工的报告？

Answer 1

最好的方法是使用分析函数RANK（）或DENSE_RANK（）...SQL> select * from (&nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; select empno&nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, sal&nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, rank() over (order by sal desc) as rnk&nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; from emp)&nbsp; 6&nbsp; where rnk <= 5&nbsp; 7&nbsp; /&nbsp; &nbsp; &nbsp;EMPNO&nbsp; &nbsp; &nbsp; &nbsp; SAL&nbsp; &nbsp; &nbsp; &nbsp; RNK---------- ---------- ----------&nbsp; &nbsp; &nbsp; 7839&nbsp; &nbsp; &nbsp; &nbsp;5000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; 7788&nbsp; &nbsp; &nbsp; &nbsp;3000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; 7902&nbsp; &nbsp; &nbsp; &nbsp;3000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; 7566&nbsp; &nbsp; &nbsp; &nbsp;2975&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; 8083&nbsp; &nbsp; &nbsp; &nbsp;2850&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; 7698&nbsp; &nbsp; &nbsp; &nbsp;2850&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 56 rows selected.SQL>出现平局时，DENSE_RANK（）压缩间隙：SQL> select * from (&nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; select empno&nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, sal&nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, dense_rank() over (order by sal desc) as rnk&nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; from emp)&nbsp; 6&nbsp; where rnk <= 5&nbsp; 7&nbsp; /&nbsp; &nbsp; &nbsp;EMPNO&nbsp; &nbsp; &nbsp; &nbsp; SAL&nbsp; &nbsp; &nbsp; &nbsp; RNK---------- ---------- ----------&nbsp; &nbsp; &nbsp; 7839&nbsp; &nbsp; &nbsp; &nbsp;5000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; 7788&nbsp; &nbsp; &nbsp; &nbsp;3000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; 7902&nbsp; &nbsp; &nbsp; &nbsp;3000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; 7566&nbsp; &nbsp; &nbsp; &nbsp;2975&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; 8083&nbsp; &nbsp; &nbsp; &nbsp;2850&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; 7698&nbsp; &nbsp; &nbsp; &nbsp;2850&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; 8070&nbsp; &nbsp; &nbsp; &nbsp;2500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 57 rows selected.SQL>您更喜欢哪种行为取决于您的业务需求。还有ROW_NUMBER（）分析函数，我们可以使用它返回精确的行数。但是，除非业务逻辑愿意在出现平局的情况下随意截断结果集，否则我们应避免使用基于行号的解决方案。要求获得五个最高值与按最高值排序的前五个记录有所不同还有使用ROWNUM伪列的非解析解决方案。这很笨拙，因为在ORDER BY子句之前应用了ROWNUM，这可能导致意外结果。几乎没有任何理由使用ROWNUM而不是ROW_NUMBER（）或排名函数之一。

Answer 2

试试这个：SELECT * FROM&nbsp;&nbsp; &nbsp; (SELECT field1, field2 FROM fields order by field1 desc)&nbsp;where rownum <= 5还请查看该资源，以获取有关rownum工作原理的更详细描述。

Answer 3

在Oracle 12c中，可以使用FETCH..FIRST ROWS..ONLY获取前5名最高薪水。&nbsp;SELECT *&nbsp; &nbsp; &nbsp; &nbsp;FROM EMPLOYEES&nbsp; &nbsp;ORDER BY SALARY DESCFETCH FIRST 5 ROWS ONLY;