LeetCode（数据库）- 平均工资：部门与公司比较

-- 解决方案(1)
WITH t1 AS(
    SELECT s.*, e.department_id FROM salary s JOIN employee e ON s.employee_id = e.employee_id 
),
t2 AS(
    SELECT SUBSTRING(pay_date, 1, 7) pay_month, AVG(amount) co_avg FROM t1 GROUP BY pay_month
),
t3 AS(
    SELECT SUBSTRING(pay_date, 1, 7) pay_month, department_id, AVG(amount) de_avg FROM t1 GROUP BY pay_month, department_id
)
SELECT t2.pay_month, department_id, CASE
WHEN t2.co_avg = t3.de_avg THEN 'same'
WHEN t2.co_avg > t3.de_avg THEN 'lower'
ELSE 'higher'
END AS comparison
FROM t2 JOIN t3 ON t2.pay_month = t3.pay_month
-- 解决方案(2)
select department_salary.pay_month, department_id,
case
  when department_avg>company_avg then 'higher'
  when department_avg<company_avg then 'lower'
  else 'same'
end as comparison
from
(
  select department_id, avg(amount) as department_avg, date_format(pay_date, '%Y-%m') as pay_month
  from salary join employee on salary.employee_id = employee.employee_id
  group by department_id, pay_month
) as department_salary
join
(
  select avg(amount) as company_avg,  date_format(pay_date, '%Y-%m') as pay_month from salary group by date_format(pay_date, '%Y-%m')
) as company_salary
on department_salary.pay_month = company_salary.pay_month;