力扣(LeetCode) - 平均工资:部门与公司比较

一. 题目

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

工资表: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

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

对于如上样例数据,结果为:

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=800。因为 8000 < 8333.33 ,所以比较结果‘lower’。

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

二. 答案

SELECT t1.发工资月份, t1.部门,
CASE 
	WHEN t1.部门平均工资 > t2.公司平均工资 THEN 'higher'
	WHEN t1.部门平均工资 < t2.公司平均工资 THEN 'lower'
	ELSE 'same'
END AS '比较结果',
t1.部门平均工资,
t2.公司平均工资
FROM
(
-- 拿到每个月份部门的平均工资
SELECT employee.department_id AS '部门', SUM(salary.amount) / COUNT(salary.employee_id) '部门平均工资', DATE_FORMAT(salary.pay_date, '%Y-%m') AS '发工资月份' FROM salary 

RIGHT JOIN employee ON salary.employee_id = employee.employee_id

GROUP BY 发工资月份, employee.department_id
) t1
INNER JOIN
(
-- 拿到每个月份公司的平均工资
SELECT DATE_FORMAT(salary.pay_date, '%Y-%m') AS '发工资月份', SUM(salary.amount) / COUNT(salary.employee_id) AS '公司平均工资'
FROM salary
GROUP BY 发工资月份
) t2 
ON t1.发工资月份 = t2.发工资月份

热门相关:最强狂兵   薄先生,情不由己   薄先生,情不由己   豪门闪婚:帝少的神秘冷妻   仗剑高歌