LeetCode--615. 平均工资：部门与公司比较

建表

drop table if EXISTS salary; create table salary ( id int, employee_id int, amounnt DECIMAL, pay_date date ); drop table if EXISTS employee; create table employee ( employee_id int, department_id int ); insert into salary values(1, 1, 9000, '2017-03-31'); insert into salary values(2, 2, 6000, '2017-03-31'); insert into salary values(3, 3, 10000, '2017-03-31'); insert into salary values(4, 1, 7000, '2017-02-28'); insert into salary values(5, 2, 6000, '2017-02-28'); insert into salary values(6, 3, 8000, '2017-02-28'); insert into employee values(1, 1); insert into employee values(2, 2); insert into employee values(3, 2);

解题思路

先求出每个部门每个月的平均工资

再求公司每个月的平均工资

连表查，case when 比较

select DATE_FORMAT(t1.pay_date,'%Y-%m') pay_month, t1.department_id, case when t1.am > t2.am then 'higher' when t1.am < t2.am then 'lower' else 'same' end comparison from ( select pay_date, department_id , avg(amount) am from salary s, employee e where s.employee_id = e.employee_id GROUP BY pay_date, department_id ) t1, ( select pay_date, avg(amount) am from salary GROUP BY pay_date ) t2 where t1.pay_date = t2.pay_date

可以拆分如下

with department_avg_salary as ( select pay_date, department_id , avg(amount) am from salary s, employee e where s.employee_id = e.employee_id GROUP BY pay_date, department_id ), Company_avg_salary as ( select pay_date, avg(amount) am from salary GROUP BY pay_date ) select DATE_FORMAT(t1.pay_date,'%Y-%m') pay_month, t1.department_id, case when t1.am > t2.am then 'higher' when t1.am < t2.am then 'lower' else 'same' end comparison from department_avg_salary t1, Company_avg_salary t2 where t1.pay_date = t2.pay_date

注意

以上答案是没问题的，但是LeetCode提交通不过，最后发现是日期的问题，先在内查询将日期转换为月份出来的答案没问题，但如果一直到最后才将日期转化为月份，2月部门2会有一个重复，希望后来人能看到，别踩坑

更正后

select t1.pay_month, t1.department_id, case when t1.am > t2.am then 'higher' when t1.am < t2.am then 'lower' else 'same' end comparison from ( select DATE_FORMAT(pay_date,'%Y-%m') pay_month, department_id , avg(amount) am from salary s, employee e where s.employee_id = e.employee_id GROUP BY pay_month, department_id ) t1, ( select DATE_FORMAT(pay_date,'%Y-%m') pay_month, avg(amount) am from salary GROUP BY pay_month ) t2 where t1.pay_month = t2.pay_month

开窗函数做法

select pay_month, department_id, case when dept_avg > com_avg then 'higher' when dept_avg < com_avg then 'lower' else 'same' end comparison from ( select distinct pay_month, department_id, avg(amount) over(partition by pay_month) com_avg, avg(amount) over(partition by pay_month, department_id) dept_avg from ( select date_format(s.pay_date, '%Y-%m') pay_month, e.department_id, s.amount from salary s left join employee e on s.employee_id = e.employee_id ) t ) t1