在两个表之间使用AVG（）函数 [英] Using AVG() function between two tables

问题描述

我有两个表，我需要确定提供任何职位最高平均薪资的公司。我的表格如下：

I have two tables, and I need to determine the company that offers the highest average salary for any position. My tables are as follows:

employer
eID (primary key), eName, location

position
eID (primary key), pName (primary key), salary)

我编写的代码确定了所有平均薪水都高于1的薪水，但我只需要查找所有平均薪水最高的

The code I wrote determines all avg salaries that are higher than one, but I need to find only the highest average salary over all

这是到目前为止的代码：

Here is my code so far:

SQL> select eName
  2  from Employer E inner join position P on E.eID = P.eID
  3  where salary > (select avg(salary) from position);

这将输出所有高于最低平均工资的薪水，但我只需要最高平均工资。我尝试使用av​​g（salary）>（从位置选择avg（salary）），但收到错误消息，提示不允许使用组功能。

This outputs all salaries that are higher than the lowest average, but I need only the highest average. I tried using avg(salary) > (select avg(salary) from position) but I received the error that group function is not allowed.

任何帮助或建议都会

推荐答案

使用：

SELECT x.eid, 
       x.ename, 
       x.avg_salary 
 FROM (SELECT e.eid,
              e.ename,
              AVG(p.salary) AS avg_salary,
              ROWNUM
         FROM EMPLOYER e
         JOIN POSTITION p ON p.eid = e.eid
     GROUP BY e.eid, e.ename
     ORDER BY avg_salary) x
 WHERE x.rownum = 1

Oracle 9i +：

Oracle 9i+:

SELECT x.eid, 
       x.ename, 
       x.avg_salary 
 FROM (SELECT e.eid,
              e.ename,
              AVG(p.salary) AS avg_salary,
              ROW_NUMBER() OVER(PARTITION BY e.eid
                                    ORDER BY AVG(p.salary) DESC) AS rank
         FROM EMPLOYER e
         JOIN POSTITION p ON p.eid = e.eid
     GROUP BY e.eid, e.ename) x
 WHERE x.rank = 1

以前，因为该问题被标记为 mysql：

Previously, because the question was tagged "mysql":

  SELECT e.eid,
         e.ename,
         AVG(p.salary) AS avg_salary
    FROM EMPLOYER e
    JOIN POSTITION p ON p.eid = e.eid
GROUP BY e.eid, e.ename
ORDER BY avg_salary
   LIMIT 1

这篇关于在两个表之间使用AVG（）函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！