具有嵌套组函数的 SQL

Question

我正在使用 Oracle 数据库 11g

我有一个像这样的表 'EMPLOYEES'...

ID  JOB_ID      SALARY
100 AD_PRES     24000
101 AD_VP       17000
102 AD_VP       17000
103 IT_PROG     9000
104 IT_PROG     6000
107 IT_PROG     4200
124 ST_MAN      5800
141 ST_CLERK    3500
142 ST_CLERK    3100
143 ST_CLERK    2600
144 ST_CLERK    2500
149 SA_MAN      10500
174 SA_REP      11000
176 SA_REP      8600
178 SA_REP      7000
200 AD_ASST     4400
201 MK_MAN      13000
202 MK_REP      6000
205 AC_MGR      12000
206 AC_ACCOUNT  8300

我想获得每个工作(job_id)及其工作的平均工资的最大值。

我第一次尝试这个，结果出错

SELECT MAX(AVG(salary)) AS max_avg_salary, job_id
FROM employees
GROUP BY job_id;

ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"
*Cause:    
*Action:

我终于用这段代码成功了

WITH emp AS (SELECT AVG(salary) AS avg_salary, job_id FROM employees GROUP BY job_id)
SELECT e1.avg_salary AS max_avg_salary, e1.job_id
FROM emp e1 JOIN (SELECT MAX(avg_salary) AS max_avg_salary FROM emp) e2
ON e1.avg_salary = e2.max_avg_salary;

MAX_AVG_SALARY  JOB_ID
24000           AD_PRES

我想知道的是..

1. 为什么我的第一个代码会出错？
2. 有没有比我的代码更好(更简单或更容易)的方法？

Answer 1

这个怎么样？

select job_id, salary
  from (  select job_id,
                 avg (salary) salary,
                 rank () over (order by avg (salary) desc) rnk
            from employees
        group by job_id)
 where rnk = 1;