本文介绍了一种使用SQL子查询的方法，通过连接多个表并分组部门来找出每个部门中薪资高于该部门平均薪资水平的员工。展示了具体的SQL查询语句，并解释了各个部分的作用。
Your approach using a subquery for the average is sound, but you need to group the subquery by department. Then you can join to the subquery by:
Department ID's are equal (equijoin), and
Employee salary is greater than than the average departmental salary (non-equijoin)
Here's the query...
SELECT emp.ename, dept.dname, emp.salary, DeptAvg.AvgSal
FROM emp
INNER JOIN works ON emp.eid = works.eid
INNER JOIN dept ON works.did = dept.did
INNER JOIN (
SELECT works.did, AVG(emp.salary) AS AvgSal
FROM emp
INNER JOIN works ON emp.eid = works.eid
GROUP BY works.did) DeptAvg
ON DeptAvg.did = works.did AND emp.salary > DeptAvg.AvgSal
This query shows employee name, department name, employee salary and average departmental salary. I did that so you can see the numbers and test it. You can remove any of the columns and the query should still work.
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
