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

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

1、avg()会自动忽略空值，不计数

2、如果人数为0，avg()会返回空值

3、ifnull(字段,0) 实现将字段中的空值转0 

对于双重计数的情况，可将上一级用group by ，下一级用case when

另外，avg的使用可以免去计数count