使用 rank 选择 Oracle SQL 中的前 10 个元组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2335924/
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
Using rank to select top 10 tuples in Oracle SQL
提问by Doug
I have the relation instructor(ID, name, dept name, salary).
我有关系讲师（ID，姓名，部门名称，工资）。
The question in our assignment asks us to: Use the rank function in SQL to write a query to nd the id and name of those instructors in the top 10 most highly paid.
我们作业中的问题要求我们：使用 SQL 中的排名函数编写一个查询，以找出收入最高的前 10 名教师的 ID 和姓名。
I'm able to rank the instructors through using select id, name, rank() over(order by(salary) desc) as sal
from instructor order by sal
我可以通过使用对教师进行排名 select id, name, rank() over(order by(salary) desc) as sal
from instructor order by sal
What is my next step in grabbing the top 10 instructors? Thanks.
我要抓住前 10 名导师的下一步是什么？谢谢。
回答by APC
Your solutionis close to the answer but it's generally better to use the value given by the RANK() function:
您的解决方案接近答案，但通常最好使用 RANK() 函数给出的值：
select id
, name
, sal
from (
select id
, name
, sal
, rank() over(order by(salary) desc) as sal_rank
from instructor
)
where sal_rank <=10
/
The problem with rownum is that it arbitrarily truncates the result set. If we have a tie for tenth place it is usually important to know that fact. Using ROWNUM returns a single random record instead.
rownum 的问题在于它任意截断了结果集。如果我们获得并列第十名，那么了解这一事实通常很重要。使用 ROWNUM 会返回单个随机记录。
Sometimes ROWNUM - or the analytic ROW_NUMBER() - will be correct. It depends on the precise business requirements.
有时 ROWNUM - 或分析 ROW_NUMBER() - 将是正确的。这取决于具体的业务需求。
回答by Doug
I just figured it out actually, using rownum...
我只是想出来了，实际上，使用 rownum ...
select id, name from(
select id, name, rank() over(order by(salary) desc) as sal
from instructor order by sal
)
where rownum <=10
Hopefully this helps someone in the future!
希望这对将来的人有所帮助！
