oracle 在两个表之间使用 AVG() 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3859495/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using AVG() function between two tables
提问by user457666
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)
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
我写的代码确定了所有高于 1 的平均工资，但我只需要找到所有最高的平均工资
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);
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.
这会输出高于最低平均值的所有工资，但我只需要最高平均值。我尝试使用 avg(salary) > (select avg(salary) from position) 但我收到了不允许使用组功能的错误。
Any help or suggestions would be greatly appreciated!
任何帮助或建议将不胜感激！
回答by OMG Ponies
Use:
用：
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+:
甲骨文 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
Previously, because the question was tagged "mysql":
以前，因为问题被标记为“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
回答by Todd Pierce
select a.eid,
a.ename,
b.avg_salary
FROM EMPLOYER a
JOIN POSTITION b ON a.eid = b.eid
WHERE b.avg_salary =(SELECT max(x.avg_salary)
FROM (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) x
) y
