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

69 阅读 0 评论 46 点赞

我是靠谱客的博主温柔皮带，最近开发中收集的这篇文章主要介绍LeetCode-615. 平均工资：部门与公司比较（困难），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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

表： 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 。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/average-salary-departments-vs-company
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

审题：部门的平均工资，和公司的平均工资的比较，根据表二看，有两个部门，一个部门有一个人，第二个部门有两个人。

工资共发放了两次。所以是四个结果，第一次发放，部门一，部门二，第二次发放部门一，部门二。

思考：每个部门的平均工资需要求部门工资和，公司整体工资平均数，对比，使用if判断结果。

解题:

解法一

每月公司平均工资是每月公司中所有员工工资的平均值。

连接表salary和表employee，按年月分组，计算每组工资的平均值。

结果命名为表A。

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

AVG() 函数返回数值列的平均值。

(
SELECT DATE_FORMAT(S.pay_date,'%Y-%m') AS `pay_month`, AVG(S.amount) AS `avg_salary`
FROM salary AS S
JOIN employee AS E ON (S.employee_id = E.employee_id)
GROUP BY `pay_month`
) AS A
-- 写
select date_format(S.pay_date,'%y-%m') as 'month' ,avg(s.amount) as 'avg_salary'
from salary as s join employee as e on (s.employee_id = e.employee_id) group by 'pay_month'

DATA_FORMAT函数，用于格式化日期。

每月每部门平均工资是每月每部门中所有员工工资的平均值。

连接表salary和表employee，按部门和年月分组，计算每组员工的平均值。

结果命名为表B。

(
SELECT E.department_id,DATE_FORMAT(S.pay_date,'%Y-%m') AS `pay_month`, AVG(S.amount) AS `dept_avg_salary`
FROM salary AS S
JOIN employee AS E ON (S.employee_id = E.employee_id)
GROUP BY E.department_id,`pay_month`
) AS B

连接表A和表B，比较相同年月中，部门平均工资与公司平均工资。

IF(
B.dept_avg_salary > A.avg_salary,
'higher', IF(B.dept_avg_salary < A.avg_salary,
'lower',
'same'
)
) AS `comparison`

或者用CASE子句。

case
when B.dept_avg_salary > A.avg_salary then 'higher'
when B.dept_avg_salary < A.avg_salary then 'lower'
ELSE 'same'
END AS `comparison`

综合以上逻辑：

SELECT B.pay_month,
B.department_id,
IF(
B.dept_avg_salary > A.avg_salary,
'higher', IF(B.dept_avg_salary < A.avg_salary,
'lower',
'same'
)
) AS `comparison`
FROM
(
SELECT DATE_FORMAT(S.pay_date,'%Y-%m') AS `pay_month`, AVG(S.amount) AS `avg_salary`
FROM salary AS S
JOIN employee AS E ON (S.employee_id = E.employee_id)
GROUP BY `pay_month`
) AS A
JOIN
(
SELECT E.department_id,DATE_FORMAT(S.pay_date,'%Y-%m') AS `pay_month`, AVG(S.amount) AS `dept_avg_salary`
FROM salary AS S
JOIN employee AS E ON (S.employee_id = E.employee_id)
GROUP BY E.department_id,`pay_month`
) AS B
ON (A.pay_month = B.pay_month)

或

SELECT B.pay_month,
B.department_id,
case
when B.dept_avg_salary > A.avg_salary then 'higher'
when B.dept_avg_salary < A.avg_salary then 'lower'
ELSE 'same'
END AS `comparison`
FROM
(
SELECT DATE_FORMAT(S.pay_date,'%Y-%m') AS `pay_month`, AVG(S.amount) AS `avg_salary`
FROM salary AS S
JOIN employee AS E ON (S.employee_id = E.employee_id)
GROUP BY `pay_month`
) AS A
JOIN
(
SELECT E.department_id,DATE_FORMAT(S.pay_date,'%Y-%m') AS `pay_month`, AVG(S.amount) AS `dept_avg_salary`
FROM salary AS S
JOIN employee AS E ON (S.employee_id = E.employee_id)
GROUP BY E.department_id,`pay_month`
) AS B
ON (A.pay_month = B.pay_month)

知识点：

多表联合查询