我要评论![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wLiQHvxV-1602129474546)(2EECCCA92B6242FCA6DC50F5E6E853FE)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SYHnV9ZK-1602129474549)(B3E763C085C74AC6BCFEFE2707849AD8)]](http://www.lhsxpumps.com/_images5/10qianwan/20201011/b_0_202010111744331570.png)
建表
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
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论