MySQL练手题--公司和部门平均工资比较（困难）

一、准备工作

Create table If Not Exists Salary (id int, employee_id int, amount int, pay_date date);
Create table If Not Exists Employee (employee_id int, department_id int);
Truncate table Salary;
insert into Salary (id, employee_id, amount, pay_date) values ('1', '1', '9000', '2017/03/31');
insert into Salary (id, employee_id, amount, pay_date) values ('2', '2', '6000', '2017/03/31');
insert into Salary (id, employee_id, amount, pay_date) values ('3', '3', '10000', '2017/03/31');
insert into Salary (id, employee_id, amount, pay_date) values ('4', '1', '7000', '2017/02/28');
insert into Salary (id, employee_id, amount, pay_date) values ('5', '2', '6000', '2017/02/28');
insert into Salary (id, employee_id, amount, pay_date) values ('6', '3', '8000', '2017/02/28');
Truncate table Employee;
insert into Employee (employee_id, department_id) values ('1', '1');
insert into Employee (employee_id, department_id) values ('2', '2');
insert into Employee (employee_id, department_id) values ('3', '2');

# 找出各个部门员工的平均薪资与公司平均薪资之间的比较结果（更高 / 更低 / 相同）。

输入：

输出： 

二、分析

三、实现 

with t as (
    select date_format(pay_date,'%Y-%m') pay_month,avg(amount) avg_amount
    from salary
    group by date_format(pay_date,'%Y-%m')   -- 公司每月平均工资
), t1 as (
    select date_format(pay_date,'%Y-%m') pay_month,department_id,avg(amount) avg_damount
    from employee e , salary s
    where e.employee_id = s.employee_id
    group by date_format(pay_date,'%Y-%m') , department_id  -- 部门每月平均工资
)
select
    t.pay_month,
    department_id,
    case
        when t1.avg_damount > t.avg_amount then 'higher'  -- 部门高于公司
        when t1.avg_damount < t.avg_amount then 'lower'   -- 部门低于公司
        else 'same'                                       -- 部门与公司相等
    end comparison
from t ,t1 where t.pay_month = t1.pay_month;

四、总结

此题有坑，需要注意给的数据是两个月的记录需要分月份去分析，公司平均的公司也是分为两个月去考虑，部门的平均工资需要对月份和部门分组求出每个部门每个月的平均工资，最后拿着部门平均工资和公司平均工资做一个case when...then 条件最后部门比公司高的输出higher，低的输出lower，相同的输出same；