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

with t as(
select
t2.department,
t2.staff_gender,
t1.normal_salary-t1.dock_salary as actual_salary
from salary_tb t1 left join staff_tb t2
on t1.staff_id=t2.staff_id)

select
t1.department,
t1.average_actual_salary,
case when 
    t2.average_actual_salary is null then 0 else
    t2.average_actual_salary end as average_actual_salary_male,
case when 
    t3.average_actual_salary is null then 0 else
    t3.average_actual_salary end as average_actual_salary_female

from

(select
department,
round(avg(actual_salary),2) as average_actual_salary
from t
group by department) t1

left join

(select
department,
round(avg(actual_salary),2) as average_actual_salary
from t
where staff_gender='male'
group by department) t2

on t1.department=t2.department

left join

(select
department,
round(avg(actual_salary),2) as average_actual_salary
from t
where staff_gender='female'
group by department) t3

on t1.department=t3.department

order by t1.average_actual_salary desc