(LeetCode 615) 平均工资：部门与公司比较

两个表，写一个查询语句，求出在每一个工资发放日，每个部门的平均工资与公司的平均工资的比较结果 （高 / 低 / 相同）

分析

1. 不同部门，不同时间的平均工资 (时间、部门 分组， 聚合)
2. 公司不同时间的平均工资
3. 比较，高、低、相同

解题

1. 时间格式化

select employee_id,amount,DATE_FORMAT(pay_date,"%Y-%m") as pay_month from salary ;

2. 不同部门，不同时间的平均工资 (时间、部门 分组， 聚合)

SELECT
    AVG(amount) as avg,a.pay_month,b.department_id
FROM
    (select employee_id,amount,DATE_FORMAT(pay_date,"%Y-%m") as pay_month from salary) a
    LEFT JOIN employee b ON a.employee_id = b.employee_id   
GROUP BY  a.pay_month DESC,b.department_id;

3. 公司不同时间的平均值

select AVG(a.amount) as avg,a.pay_month from (select employee_id,amount,DATE_FORMAT(pay_date,"%Y-%m") as pay_month from salary) a GROUP BY a.pay_month;

4. 比较

select a.pay_month,a.department_id,
case
    WHEN a.avg > b.avg then "higher"
    WHEN a.avg = b.avg then "same"
    ELSE "lower"
    END AS comparison
from (
SELECT
    AVG(amount) as avg,a.pay_month,b.department_id
FROM
    (select employee_id,amount,DATE_FORMAT(pay_date,"%Y-%m") as pay_month from salary) a
    LEFT JOIN employee b ON a.employee_id = b.employee_id   
GROUP BY  a.pay_month DESC,b.department_id) a

left JOIN (select AVG(a.amount) as avg,a.pay_month from (select employee_id,amount,DATE_FORMAT(pay_date,"%Y-%m") as pay_month from salary) a GROUP BY a.pay_month) b

on a.pay_month = b.pay_month

注意点

1. case when 的用法
2. DATE_FORMAT(date,format) 函数的使用
3. GROUP BY 多个列分组