LeetCode题解(0615)：平均工资_部门与公司比较(SQL)

题目：原题链接（困难）

标签：SQL

解法一：

SELECT T.pay_month,
       department_id,
       CASE
           WHEN T.department_avg > A.company_avg THEN 'higher'
           WHEN T.department_avg < A.company_avg THEN 'lower'
           ELSE 'same'
           END AS comparison
FROM (SELECT DATE_FORMAT(salary.pay_date, '%Y-%m') AS pay_month,
             department_id,
             AVG(amount) AS department_avg
      FROM salary
               LEFT JOIN
           employee ON employee.employee_id = salary.employee_id
      GROUP BY pay_month, department_id) AS T
         LEFT JOIN
     (SELECT DATE_FORMAT(salary.pay_date, '%Y-%m') AS pay_month,
             AVG(amount) AS company_avg
      FROM salary
      GROUP BY pay_date) AS A ON A.pay_month = T.pay_month
GROUP BY pay_month, department_id
ORDER BY department_id, pay_month;

最后

以上就是鲜艳镜子为你收集整理的LeetCode题解(0615)：平均工资_部门与公司比较(SQL)的全部内容，希望文章能够帮你解决LeetCode题解(0615)：平均工资_部门与公司比较(SQL)所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。