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

with tmp1 as (
    select staff_name, staff_gender, department,
    normal_salary - dock_salary as actual_salary
    from staff_tb
    left join salary_tb using(staff_id)
),
avg_actucal as (
    select department, round(avg(actual_salary),2) as average_actual_salary
    from tmp1
    group by department
),
avg_actucal_male as (
    select department, 
    round(avg(actual_salary),2) as average_actual_salary_male
    from tmp1
    where staff_gender = 'male'
    group by department
),
avg_actucal_female as (
    select department, round(avg(actual_salary),2) as average_actual_salary_female
    from tmp1
    where staff_gender = 'female'
    group by department
)

select avg_actucal.department, average_actual_salary, 
case when average_actual_salary_male is null then 0.00 else average_actual_salary_male end as average_actual_salary_male, 
case when average_actual_salary_female is null then 0.00 else average_actual_salary_female end as average_actual_salary_female
from avg_actucal 
left join avg_actucal_male using(department)
left join avg_actucal_female using(department)
order by average_actual_salary desc

我的代码好长，没想到用coalesce函数和ifnull函数