获得每个组的最高结果(在 Oracle 中) [英] Get top results for each group (in 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.

这篇关于获得每个组的最高结果(在 Oracle 中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！