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

不要写else 0 ,else 0 算均值会出错， 最后将空处理为0

select department , round(avg(sal),2) as average_actual_salary
, round(ifnull(avg(case when staff_gender='male' then sal end ),0),2) as average_actual_salary_male
, round(ifnull(avg(case when staff_gender='female' then sal  end ),0),2) as average_actual_salary_female
from (
select department , staff_gender,  normal_salary - dock_salary as sal
from staff_tb s inner join salary_tb sa on s.staff_id = sa.staff_id
)t
group by department
order by 2 desc

不要写else 0 ,else 0 算均值会出错， 最后将空处理为0