获得每个组的最高结果(在 Oracle 中)
我如何能够在多个组中获得 N 个结果一个 oracle 查询.
How would I be able to get N results for several groups in an oracle query.
例如，给定下表:
|--------+------------+------------|
| emp_id | name | occupation |
|--------+------------+------------|
| 1 | John Smith | Accountant |
| 2 | Jane Doe | Engineer |
| 3 | Jack 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?
推荐答案
这会产生您想要的结果，并且它不使用供应商特定的 SQL 功能，例如 TOP N 或 RANK().
This produces what you want, and it uses no vendor-specific SQL features like TOP N or 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;
在这个例子中，它给出了每个职业的 emp_id 值最低的三名员工.您可以更改不等式比较中使用的属性，使其按姓名或其他方式提供顶级员工.
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.
相关文章