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

[astak16]

题目

求出每一薪资发放日，每个部门的平均薪资与公司的平均薪资比较的结果（高/低/相同）

create table salary (id int, employee_id int, amount int, pay_date date);
insert into salary (id, employee_id, amount, pay_date) values 
('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');

create table employee (employee_id int, department_id int);
insert into employee (employee_id, department_id) values 
('1', '1'),
('2', '2'),
('3', '2');

SQL：方法一

select 
	distinct date_format(pay_date, '%Y-%m') as pay_month,
	department_id,
	(case when avg_department > avg_company then 'higher'
				when avg_department < avg_company then 'lower'
				else 'same'
	end) as comparison
from (
	select
		pay_date,
		department_id,
		avg(amount) as avg_department
	from salary join employee using(employee_id)
	group by pay_date, department_id
) as temp1 join (
	select pay_date, avg(amount) as avg_company from salary group by pay_date
) as temp2 using(pay_date)

--- 等价于

select 
	pay_month,
	department_id,
	(case when avg_department > avg_company then 'higher'
				when avg_department < avg_company then 'lower'
				else 'same'
	end) as comparison
from (
	select
		date_format(pay_date, '%Y-%m') as pay_month, 
		department_id,
		avg(amount) as avg_department
	from salary join employee using(employee_id)
	group by pay_month, department_id
) as temp1 join (
	select 
		date_format(pay_date, '%Y-%m') as pay_month, 
		avg(amount) as avg_company
	from salary group by pay_month
) as temp2 using(pay_month)

解析

这题有两个重点：

1. 部门每个月的平均薪资
2. 公司每个月的平均薪资

计算部门每个月的平均薪资，将 salary 和 employee 用 employee_id 连接，并且按照 ，计算出部门薪资平均值 avg_department ， pay_month 和 department_id 进行分组，将它作为临时表 temp1

计算公司每个月的平均薪资比较简单，直接对 salary 表按照 pay_date 进行分组，并且计算出公司薪资平均值 avg_company，将它作为临时表 temp2

将 temp1 和 temp2 用 pay_date 连接起来，使用 case ... when ... end 语句比较avg_department 和 avg_company 的大小后输出 same 、 higher、 lower

因为这里输出的都是日期 date ，所以这里要使用 date_format() 对它进行日期格式化。

这里要注意一点的是因为 temp1 和 temp2 都是用 date 分组的，而最后查出来的数据只需要月份，所以这里可能会出现重复的数据，需要在最后使用 distinct 去重，或者呢在 temp1 和 temp2 是就直接使用 month 进行分组。