题解 | 各个部门实际平均薪资和男女员工实际平均薪资
各个部门实际平均薪资和男女员工实际平均薪资
https://www.nowcoder.com/practice/e8272685d07347cc88667f31f7989231
# with t1 as( # select # department, # staff_gender, # round(sum(normal_salary-dock_salary),2) salary, # count(a.staff_id) staffs, # round(sum(normal_salary-dock_salary)/count(a.staff_id),2) average_actual_salary # from # salary_tb a inner join staff_tb b on a.staff_id=b.staff_id # group by department,staff_gender # ), # t2 as( # select # department, # sum(if(staff_gender='female',salary,0)) salary_female, # sum(if(staff_gender='male',salary,0)) salary_male, # sum(if(staff_gender='female',staffs,0)) staffs_female, # sum(if(staff_gender='male',staffs,0)) staffs_male, # sum(if(staff_gender='female',average_actual_salary,0)) average_actual_salary_female, # sum(if(staff_gender='male',average_actual_salary,0)) average_actual_salary_male # from # t1 # group by department # ) # select # department, # round((salary_female+salary_male)/(staffs_female+staffs_male),2) average_actual_salary,average_actual_salary_male, # average_actual_salary_female # from # t2 # order by average_actual_salary desc select department, ifNull(round(avg(normal_salary-dock_salary),2),0.00) average_actual_salary, ifNull(round(avg(if(staff_gender='male',normal_salary-dock_salary,null)),2),0.00)average_actual_salary_male, round(avg(if(staff_gender='female',normal_salary-dock_salary,null)),2) average_actual_salary_female from salary_tb a inner join staff_tb b on a.staff_id=b.staff_id group by department order by average_actual_salary desc