Leetcode力扣 MySQL数据库 615 平均工资：部门与公司比较615 平均工资：部门与公司比较

78 阅读 0 评论 52 点赞

我是靠谱客的博主大气可乐，最近开发中收集的这篇文章主要介绍Leetcode力扣 MySQL数据库 615 平均工资：部门与公司比较615 平均工资：部门与公司比较，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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

SQL架构

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

给如下两个表，写一个查询语句，求出在每一个工资发放日，每个部门的平均工资与公司的平均工资的比较结果（高 / 低 / 相同）。

表： salary

| id | employee_id | amount | pay_date |
|----|-------------|--------|------------|
| 1 | 1 | 9000 | 2017-03-31 |
| 2 | 2 | 6000 | 2017-03-31 |
| 3 | 3 | 10000 | 2017-03-31 |
| 4 | 1 | 7000 | 2017-02-28 |
| 5 | 2 | 6000 | 2017-02-28 |
| 6 | 3 | 8000 | 2017-02-28 |

employee_id 字段是表 employee 中 employee_id 字段的外键。

| employee_id | department_id |
|-------------|---------------|
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |

对于如上样例数据，结果为：

| pay_month | department_id | comparison |
|-----------|---------------|-------------|
| 2017-03 | 1 | higher |
| 2017-03 | 2 | lower |
| 2017-02 | 1 | same |
| 2017-02 | 2 | same |

解释

在三月，公司的平均工资是 (9000+6000+10000)/3 = 8333.33...

由于部门 '1' 里只有一个 employee_id 为 '1' 的员工，所以部门 '1' 的平均工资就是此人的工资 9000 。因为 9000 > 8333.33 ，所以比较结果是 'higher'。

第二个部门的平均工资为 employee_id 为 '2' 和 '3' 两个人的平均工资，为 (6000+10000)/2=8000 。因为 8000 < 8333.33 ，所以比较结果是 'lower' 。

在二月用同样的公式求平均工资并比较，比较结果为 'same' ，因为部门 '1' 和部门 '2' 的平均工资与公司的平均工资相同，都是 7000 。

解题

-- 1、计算公司每个月的平均工资

select avg(amount) as company_avg,  date_format(pay_date, '%Y-%m') as pay_month
from salary_615
group by date_format(pay_date, '%Y-%m');

-- 2、计算每个部门每个月的平均工资

select department_id, avg(amount) as department_avg, date_format(pay_date, '%Y-%m') as pay_month
from salary_615
join employee_615 on salary_615.employee_id = employee_615.employee_id
group by department_id, pay_month;

-- 3、将 2 中的表和之前的公司数据作比较并求出结果

select department_salary.pay_month, department_id,
case
  when department_avg>company_avg then 'higher'
  when department_avg<company_avg then 'lower'
  else 'same'
end as comparison
from
(
  select department_id, avg(amount) as department_avg, date_format(pay_date, '%Y-%m') as pay_month
  from salary_615 join employee_615 on salary_615.employee_id = employee_615.employee_id
  group by department_id, pay_month
) as department_salary
join
(
  select avg(amount) as company_avg,  date_format(pay_date, '%Y-%m') as pay_month from salary_615 group by date_format(pay_date, '%Y-%m')
) as company_salary
on department_salary.pay_month = company_salary.pay_month;