一、准备工作

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_amountfrom salarygroup by date_format(pay_date,'%Y-%m')   -- 公司每月平均工资
), t1 as (select date_format(pay_date,'%Y-%m') pay_month,department_id,avg(amount) avg_damountfrom employee e , salary swhere e.employee_id = s.employee_idgroup by date_format(pay_date,'%Y-%m') , department_id  -- 部门每月平均工资
)
selectt.pay_month,department_id,casewhen 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；