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

发布于 2024-09-01 11:12:06 字数 817 浏览 5 评论 0原文

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

假设我有一个员工表：

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

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

我确信我错过了一些东西这里真的很明显，有人能指出我正确的方向吗？

原文

Hopefully that title makes sense...

Let's say I have an employee table:

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

What I'd like to do is write a query that will give me the above table, along with an averaged salary column, based on the employee title:

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

I've tried doing this with a sub-query along the lines of:

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

But I've come to realize that the sub-query is executed first, and has no knowledge of the table alias'd e

I'm sure I'm missing something REALLY obvious here, can anyone point me in the right diretion?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

绿阴红影里的.如风往事 2024-09-08 11:12:06

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

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

回复收藏 0 原文

~没有更多了~