SQL 获得每个组的最高结果（在 Oracle 中）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/134958/
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
Get top results for each group (in Oracle)
提问by oneself
How would I be able to get N results for several groups in an oracle query.
我如何能够在 oracle 查询中为多个组获得 N 个结果。
For example, given the following table:
例如，给定下表：
|--------+------------+------------|
| emp_id | name | occupation |
|--------+------------+------------|
| 1 | John Smith | Accountant |
| 2 | Jane Doe | Engineer |
| 3 | Hyman Black | Funnyman |
|--------+------------+------------|
There are many more rows with more occupations. I would like to get three employees (lets say) from each occupation.
有更多的行有更多的职业。我想从每个职业中获得三名员工（比方说）。
Is there a way to do this without using a subquery?
有没有办法在不使用子查询的情况下做到这一点？
采纳答案by Bill Karwin
This produces what you want, and it uses no vendor-specific SQL features like TOP N or RANK().
这会产生您想要的结果，并且它不使用特定于供应商的 SQL 功能，例如 TOP N 或 RANK()。
SELECT MAX(e.name) AS name, MAX(e.occupation) AS occupation
FROM emp e
LEFT OUTER JOIN emp e2
ON (e.occupation = e2.occupation AND e.emp_id <= e2.emp_id)
GROUP BY e.emp_id
HAVING COUNT(*) <= 3
ORDER BY occupation;
In this example it gives the three employees with the lowest emp_id values per occupation. You can change the attribute used in the inequality comparison, to make it give the top employees by name, or whatever.
在这个例子中，它给出了每个职业具有最低 emp_id 值的三名员工。您可以更改不等式比较中使用的属性，使其按名称或其他方式提供顶级员工。
回答by jop
I don't have an oracle instance handy right now so I have not tested this:
我现在手头没有一个 oracle 实例，所以我没有测试过这个：
select *
from (select emp_id, name, occupation,
rank() over ( partition by occupation order by emp_id) rank
from employee)
where rank <= 3
Here is a link on how rank works: http://www.psoug.org/reference/rank.html
这是关于排名如何工作的链接：http: //www.psoug.org/reference/rank.html
回答by trung
Add RowNum to rank :
将 RowNum 添加到排名：
select * from
(select emp_id, name, occupation,rank() over ( partition by occupation order by emp_id,RowNum) rank
from employee)
where rank <= 3
回答by billjamesdev
I'm not sure this is very efficient, but maybe a starting place?
我不确定这是否非常有效，但也许是一个起点？
select *
from people p1
join people p2
on p1.occupation = p2.occupation
join people p3
on p1.occupation = p3.occupation
and p2.occupation = p3.occupation
where p1.emp_id != p2.emp_id
and p1.emp_id != p3.emp_id
This should give you rows that contain 3 distinct employees all in the same occupation. Unfortunately, it will give you ALL combinations of those.
这应该为您提供包含 3 个不同员工的行，所有员工都从事相同的职业。不幸的是，它会给你所有这些组合。
Can anyone pare this down please?
任何人都可以减少这个吗？
回答by Leon Tayson
tested this in SQL Server (and it uses subquery)
在 SQL Server 中对此进行了测试（并且它使用子查询）
select emp_id, name, occupation
from employees t1
where emp_id IN (select top 3 emp_id from employees t2 where t2.occupation = t1.occupation)
just do an ORDER by in the subquery to suit your needs
只需在子查询中执行 ORDER by 即可满足您的需求
