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

本文地址：https://blog.csdn.net/qq_42363032/article/details/108961540