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

一. 题目：

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

idemployee_idamountpay_date1190002017-03-312260002017-03-3133100002017-03-314170002017-02-285260002017-02-286380002017-02-28职员表：employee

employee_iddepartment_id112232

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

对于如上样例数据，结果为：
pay_monthdepartment_idcomparison2017-031higher2017-032lower2017-021same2017-022same解释：

​        在三月，公司的平均工资是 (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。
二. 答案

1. SELECT t1.发工资月份, t1.部门,
2. CASE
3.         WHEN t1.部门平均工资 > t2.公司平均工资 THEN 'higher'
4.         WHEN t1.部门平均工资 < t2.公司平均工资 THEN 'lower'
5.         ELSE 'same'
6. END AS '比较结果',
7. t1.部门平均工资,
8. t2.公司平均工资
9. FROM
10. (
11. -- 拿到每个月份部门的平均工资
12. SELECT employee.department_id AS '部门', SUM(salary.amount) / COUNT(salary.employee_id) '部门平均工资', DATE_FORMAT(salary.pay_date, '%Y-%m') AS '发工资月份' FROM salary
14. RIGHT JOIN employee ON salary.employee_id = employee.employee_id
16. GROUP BY 发工资月份, employee.department_id
17. ) t1
18. INNER JOIN
19. (
20. -- 拿到每个月份公司的平均工资
21. SELECT DATE_FORMAT(salary.pay_date, '%Y-%m') AS '发工资月份', SUM(salary.amount) / COUNT(salary.employee_id) AS '公司平均工资'
22. FROM salary
23. GROUP BY 发工资月份
24. ) t2
25. ON t1.发工资月份 = t2.发工资月份

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！