OracleSQL从组中查找顶N行

Question

我正在尝试查询行的顶部N，但在OracleSQL中有一个组

例如：

+-----+----------+----+------+
|JobID|JobTitle  |Pay |Name  |
+-----+----------+----+------+
|012  |Manager   |400 |Bob   |
|012  |Manager   |400 |Gemma |
|012  |Manager   |400 |Ash   |
|020  |Supervisor|400 |Dan   |
|020  |Supervisor|400 |Hannah|
|013  |Clerk     |300 |Sarah |
+-----+----------+----+------+

从上面的例子，我想找到所有的雇员在一个职位下，然后只显示前3名薪酬最高的工作，然后按薪酬排序。

+-----+----------+----+------+
|JobID|JobTitle  |Pay |Name  |
+-----+----------+----+------+
|02   |Manager   |400 |Bob   |
|02   |Manager   |400 |Gemma |
|02   |Manager   |400 |Ash   |
|04   |Supervisor|400 |Dan   |
|04   |Supervisor|400 |Hannah|
|03   |Clerk     |300 |Sarah |
|01   |Cleaner   |200 |Scruff|
|01   |Cleaner   |200 |Fry   |
+-----+----------+----+------+

就是桌子。

我尝试使用子查询，然后添加到rownum <= 3上，但最后仍然没有找到想要的结果。

    SELECT * FROM 
      ( SELECT JobID, MAX(Pay) AS Pay, JobTitle, EmpID, Name 
        FROM Employees,Jobs
        WHERE JobID = Employees.Job_ID
        GROUP BY JobID, Pay, EmpID, JobTitle,
        ORDER BY Pay DESC)
     WHERE ROWNUM <= 3;

Answer 1

最好使用窗口函数来完成：

select jobid, jobtitle, pay, name
from (
  select j.jobid, j.jobtitle, e.pay, e.name, 
         row_number() over (partition by j.jobid order by e.pay desc) as rn
  from employees e 
    join jobs j on j.jobid = e.job_id
) 
where rn <= 3
order by jobid, jobtitle, pay desc;

我还将where子句中过时的隐式联接更改为显式JOIN。