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

select  distinct department,
        round(avg(salary) over(partition by department),2) as average_actual_salary,
        round(coalesce(avg(case when staff_gender = 'male' then salary else null end) over(partition by department),0),2)   as average_actual_salary_male,
        round(coalesce(avg(case when staff_gender = 'female' then salary else null end) over(partition by department),0),2)   as average_actual_salary_female
from (
    select *,
          round(normal_salary - dock_salary,2) as salary 
    from staff_tb left join salary_tb using(staff_id)
) t 
order by average_actual_salary desc