MySQL 查询 - 赚取超过平均工资

Question

我正在努力让这个查询起作用。基本上是让所有没有在牛津布鲁克斯大学获得学位的人现在的薪水超过整个数据库的平均薪水。我已经搜索了几个小时试图找到解决方案。请随时提供建议，以便我可以从中学习并解决我的问题。谢谢

SELECT a.personid,
       a.firstname,
       a.lastname,
       b.placeofstudy,
       AVG(c.salary)
FROM   person a
       INNER JOIN award d
               ON a.personid = d.personid
       INNER JOIN qualification b
               ON d.qualid = b.qualid
       INNER JOIN job c
               ON a.personid = c.personid
WHERE  placeofstudy = 'Oxford Brookes'
GROUP  BY a.personid;  

这目前不会产生任何错误，但不会返回平均工资，只是每个人当前的工资。即使我添加“拥有 c.salary > avg(c.salary)”，它也拒绝工作。

Answer 1

select a.personid, a.firstname, a.lastname, b.placeofstudy, c.salary, (select avg(salary) from job) as AvgSalary
from person a
inner join award d on a.personid = d.personid
inner join qualification b on d.qualid = b.qualid
inner join job c on a.personid = c.personid
where placeofstudy = 'Oxford Brookes'
    and c.salary > (select avg(salary) from job)