题解 | 各个部门实际平均薪资和男女员工实际平均薪资

select distinct department,round(average_actual_salary, 2) as average_actual_salary, 
round(ifnull(average_actual_salary_male,0), 2) as average_actual_salary_male,
round(ifnull(average_actual_salary_female,0), 2) as average_actual_salary_female
from
(select a.department, 
avg(b.normal_salary - b.dock_salary) over(partition by a.department) as average_actual_salary,
avg(if(staff_gender = 'male',b.normal_salary - b.dock_salary,null)) over(partition by a.department) as average_actual_salary_male,
avg(if(staff_gender = 'female',b.normal_salary - b.dock_salary,null)) over(partition by a.department) as average_actual_salary_female
from staff_tb a 
join salary_tb b on a.staff_id = b.staff_id) t
order by average_actual_salary desc