sql面试题

select a.staffName,a.salary,c.areaName,b.deptName from staff a,department b,area c,
(
select b.deptId,max(a.salary) maxSalary 
from staff a,department b where a.deptId=b.deptId
and a.deptId in (
select a.deptId from (select b.deptId,sum(a.salary) deptSalary 
from staff a,department b where a.deptId=b.deptId
group by b.deptId
) a,(
select max(b.deptSalary) deptSalary from (
select sum(a.salary) deptSalary 
from staff a,department b where a.deptId=b.deptId
group by b.deptId
) b ) b where a.deptSalary=b.deptSalary
)
group by b.deptId
) d
where a.deptId=b.deptId and b.deptId=d.deptId and c.areaId=b.areaId
and a.salary=d.maxSalary

image