# Write your MySQL query statement below
615. 平均工资：部门与公司比较
表：Salary
+-------------+------+ | 列名 | 类型 | +-------------+------+ | id | int | | employee_id | int | | amount | int | | pay_date | date | +-------------+------+ 在 SQL 中，id 是该表的主键列。 该表的每一行表示一个员工一个月的薪资。 employee_id 是来自 Employee 表的外键（reference 列）。
表： Employee
+---------------+------+ | 列名 | 类型 | +---------------+------+ | employee_id | int | | department_id | int | +---------------+------+ 在 SQL 中，employee_id 是该表的主键列。 该表的每一行表示一个员工所属的部门。
找出各个部门员工的平均薪资与公司平均薪资之间的比较结果（更高 / 更低 / 相同）。
以 任意顺序 返回结果表。
结果格式如下所示。
示例 1：
输入： 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 表: +-------------+---------------+ | employee_id | department_id | +-------------+---------------+ | 1 | 1 | | 2 | 2 | | 3 | 2 | +-------------+---------------+ 输出： +-----------+---------------+------------+ | pay_month | department_id | comparison | +-----------+---------------+------------+ | 2017-02 | 1 | same | | 2017-03 | 1 | higher | | 2017-02 | 2 | same | | 2017-03 | 2 | lower | +-----------+---------------+------------+ 解释： 在三月，公司的平均工资是 (9000+6000+10000)/3 = 8333.33... 部门 '1' 的平均薪资是 9000，因为该部门只有一个员工，其员工号为 '1'。因为 9000 > 8333.33，所以比较结果为 'higher' 部门 '2' 的平均薪资是（6000 + 10000）/ 2 = 8000，该平均薪资是员工号 '2' 和 '3' 的薪资的平均值。因为 8000 < 8333.33，比较结果为 'lower'。 根据同样的公式，对于二月份的平均薪资比较，结果为 'same'，因为部门 '1' 和 '2' 都与公司的平均薪资相同，即为 7000。
原站题解
mysql 解法, 执行用时: 405 ms, 内存消耗: 0 B, 提交时间: 2023-10-16 17:00:28
# Write your MySQL query statement below
# 考虑一个月有多次 pay_date
SELECT a.pay_month, b.department_id,
CASE
WHEN b.departmentaverage < a.companyaverage THEN 'lower'
WHEN b.departmentaverage > a.companyaverage THEN 'higher'
ELSE 'same'
END AS comparison
FROM
### 计算全公司平均值
(SELECT date_format(pay_date, '%Y-%m') AS pay_month, AVG(amount) AS companyaverage
FROM salary
GROUP BY date_format(pay_date, '%Y-%m')) a
INNER JOIN
### 计算部门平均值
(SELECT date_format(pay_date, '%Y-%m') AS pay_month, department_id, AVG(amount) AS departmentaverage
FROM salary s
INNER JOIN employee e
ON s.employee_id = e.employee_id
GROUP BY date_format(pay_date, '%Y-%m'), department_id) b
ON a.pay_month = b.pay_month
mysql 解法, 执行用时: 230 ms, 内存消耗: 0 B, 提交时间: 2023-10-16 16:59:52
# Write your MySQL query statement below
select distinct * from
(select
date_format(s.pay_date, '%Y-%m') as pay_month,
e.department_id,
case
when avg(amount) over (partition by e.department_id, s.pay_date)
< avg(amount) over (partition by s.pay_date) then 'lower'
when avg(amount) over (partition by e.department_id, s.pay_date)
> avg(amount) over (partition by s.pay_date) then 'higher'
else 'same' end as comparison
from salary s left join employee e
on s.employee_id = e.employee_id) a
order by 1 desc