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

select s.department, round(avg(normal_salary-dock_salary),2)average_actual_salary,
if(avg_male is null,0.00,avg_male) average_actual_salary_male,
if(avg_female is null,0.00,avg_female) average_actual_salary_female
from staff_tb s join salary_tb sa using(staff_id)
left join
(select department,round(avg(normal_salary-dock_salary),2) avg_male
from staff_tb s join salary_tb sa using(staff_id)
group by department,staff_gender
having staff_gender='male') m using(department)
left join
(select department,round(avg(normal_salary-dock_salary),2) avg_female
from staff_tb s join salary_tb sa using(staff_id)
group by department,staff_gender
having staff_gender='female') fe
using(department)
group by s.department,avg_male,avg_female
order by average_actual_salary desc;

select sf.department,
round(avg(sy.normal_salary - sy.dock_salary), 2) as average_actual_salary,
ifnull(round(avg(if(sf.staff_gender = 'male', sy.normal_salary - sy.dock_salary, null)), 2), 0.00) as average_actual_salary_male,
ifnull(round(avg(if(sf.staff_gender = 'female', sy.normal_salary - sy.dock_salary, null)), 2), 0.00) as average_actual_salary_female
from staff_tb sf
join salary_tb sy on sf.staff_id = sy.staff_id
group by department
order by average_actual_salary desc;
我还是写复杂了，下面一种着实优雅啊