Leetcode 615. 平均工资：部门与公司比较

select
distinct 
date_format(a.pay_date ,'%Y-%m')pay_month
,a.department_id
,case when a.dep_average>all_average then 'higher'
      when a.dep_average=all_average then 'same'
      when a.dep_average<all_average then 'lower' 
 end comparison
from(
    select 
    s.pay_date       pay_date
    ,e.department_id department_id
    ,round(sum(s.amount)/count(s.employee_id),2) dep_average
    from salary s left join employee e on s.employee_id=e.employee_id 
    where e.employee_id is not null 
    group by s.pay_date,e.department_id  
)a left join (
select pay_date,round(sum(amount)/count(*),2) all_average  from salary group by pay_date
)b on a.pay_date=b.pay_date