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

with avg_sal as (
    select 
        department,
        staff_gender,
        normal_salary-dock_salary as salary
    from
        staff_tb
        join salary_tb on staff_tb.staff_id = salary_tb.staff_id
)
select
    department,
    ifnull(round(avg(salary), 2), 0.00) as average_actual_salary,
    ifnull(round(avg(if(staff_gender='male', salary,null)),2),0.00) as average_actual_salary_male,
    ifnull(round(avg(if(staff_gender='female',salary,null)),2),0.00) as average_actual_salary_female
from avg_sal
group by department
order by average_actual_salary desc
    

或

with avg_dep as (
     select 
         department,
         round(avg(normal_salary-dock_salary), 2) as average_actual_salary
     from
         staff_tb
         join salary_tb on staff_tb.staff_id = salary_tb.staff_id
     group by 
         department
 ),
 avg_dep_male as (
     select 
         department,
         round(avg(normal_salary-dock_salary), 2) as average_actual_salary_male
     from
         staff_tb
         join salary_tb on staff_tb.staff_id = salary_tb.staff_id
     where
         staff_gender = 'male'
     group by 
         department
 ),
 avg_dep_female as (
     select 
         department,
         round(avg(normal_salary-dock_salary), 2) as average_actual_salary_female
     from
         staff_tb
         join salary_tb on staff_tb.staff_id = salary_tb.staff_id
     where
         staff_gender = 'female'
     group by 
         department
 )
 select
     avg_dep.department,
     ifnull(average_actual_salary, 0.00) as average_actual_salary,
     ifnull(average_actual_salary_male, 0.00) as average_actual_salary_male,
     ifnull(average_actual_salary_female, 0.00) as average_actual_salary_female
 from
     avg_dep
     left join avg_dep_male on avg_dep.department = avg_dep_male.department
     left join avg_dep_female on avg_dep.department = avg_dep_female.department
 order by 
     average_actual_salary desc