使用子查询查找每个部门员工的平均工资，按年龄对部门内的员工进行排序

Question

表员工

Name    Age Department      Salary
Ramesh  20  Finance         50,000
Deep    25  Sales           30,000
Suresh  22  Finance         50,000
Ram     28  Finance         20,000
Pradeep 22  Sales           20,000

如何使用子查询解决？

使用窗口功能，

select name,age,department,salary.
avg(salary) OVER(partition by department order by age) AS avg_salary from Employee

输出

Name    Age Department  Salary  Avg_Salary
Ramesh  20  Finance        50,000     40, 000
Suresh  22  Finance        50,000     40, 000
Ram     28  Finance        20,000     40, 000
Pradeep 22  Sales          20,000     25, 000
Deep    25  Sales          30,000     25, 000

Answer 1

您可以使用：

select e.Name,
e.Age,
e.Department,
e.Salary,
tbl.avg_salary
from employees e
inner join ( select Department,
avg(Salary) as avg_salary 
from employees
group by Department
) as tbl on e.Department=tbl.Department
order by e.Department,e.Age asc;

https://dbfiddle.uk/IYBkuzw-