一个SQL实现薪水大于所在部门平均薪水的员工

id    empName    DeptId    empSalary

1          张三              1            2500
2          李四              1            2400
3          王二              2            2600
4          麻子              2            2500

select * from emp e1 where e1.empSalary > (select avg(empSalary) from emp e2 where e1.DeptId = e2.DeptId)

查询结果：
1          张三              1            2500
3          王二              2            2600

 

补充：

今天在跟同事交流的时候，发现昨天少考虑了一样东西，性能。
这个问题还有一种解决方式，SQL如下：

select  e1.*
from emp e1 ,(select DeptId,avg(empSalary) as deptSalary from emp group by DeptId) as e2
where e1.DeptId = e2.DeptId and e1.empSalary > e2.deptSalary（测试通过）

现在来分析这两种写法的差别在哪。第一种写法看似更简捷，语句少了不少，第二种似乎更麻烦。其实不然，第一种写法是比第二种效率更低，为什么。原因就在于 emp中员工每次比较薪水时，都会计算一次该员工所属部门的平均薪水，这样emp中有4条记录，就会计算4次。而第二种方式只计算一次部门的评价薪水。如 果表中数据量比较大，对性能的影响会更明显，所以第二种方式更佳。