返回所有薪资高于本部门平均薪资的员工

Question

​

​

返回所有薪资高于本部门平均薪资的员工

Answer 1

通过连接到计算每个部门的平均工资的子查询

select empno, ename, sal, avg_dept_sal, emp.deptno
from employees as emp
join (
  select deptno
  , avg(sal) as avg_dept_sal 
  from employees
  group by deptno
) d on d.deptno = emp.deptno
where sal > avg_dept_sal