mysql - 使用 SQL 基于当前行值对数据子集进行聚合函数

Question

希望这个标题有意义......

假设我有一个员工表:

ID | Name | Title   | Salary
----------------------------
1  | Bob  | Manager | 15285
2  | Joe  | Worker  | 10250
3  | Al   | Worker  | 11050
4  | Paul | Manager | 16025
5  | John | Worker  | 10450

我想要做的是编写一个查询，该查询将为我提供上表以及基于员工职位的平均工资列:

ID | Name | Title   | Salary | Pos Avg
--------------------------------------
1  | Bob  | Manager | 15285  | 15655
2  | Joe  | Worker  | 10250  | 10583
3  | Al   | Worker  | 11050  | 10583
4  | Paul | Manager | 16025  | 15655
5  | John | Worker  | 10450  | 10583

我尝试使用子查询来执行此操作:

Select *, (select Avg(e2.salary) from employee e2 where e2.title = e.title) from employee e

但是我开始意识到子查询是首先执行的，并且不知道表别名'd e

我确信我在这里遗漏了一些非常明显的东西，有人能指出我正确的方向吗？

Answer 1

SELECT e.Id
     , e.Name
     , e.Title
     , e.Salary
     , g.PosAvg
  FROM employee e
     , (SELECT e.Title
             , avg(Salary) PosAvg
          FROM employee e
       GROUP BY e.Title) g
   WHERE e.Title = g.Title