获得每个组的最佳结果(在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   |
|--------+------------+------------|

更多行，更多职业.我想得到 每个职业有3名员工(可以说).

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屋！