题目难度

困难

题目描述

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

正确答案

SELECT pay_month,department_id,    
	CASE    
	WHEN ROUND(AVG(amount),2)>com_amount THEN 'higher'     
	WHEN ROUND(AVG(amount),2)=com_amount THEN 'same'     
	ELSE 'lower' END comparison
FROM     
	(
	SELECT t1.*,t2.com_amount    
	FROM         
		(SELECT employee_id,amount,DATE_FORMAT(pay_date, '%Y-%m') AS pay_month        
		FROM salary) AS t1    
	LEFT JOIN         
		(SELECT DATE_FORMAT(pay_date, '%Y-%m') AS pay_month, ROUND(AVG(amount),2) AS com_amount         
		FROM salary         
		GROUP BY pay_month) AS t2    
	ON t1.pay_month=t2.pay_month
	) t3
LEFT JOIN employee AS e
ON t3.employee_id=e.employee_id
GROUP BY t3.pay_month,e.department_id
ORDER BY department_id ASC,pay_month ASC

思想

• 先根据发工资月，计算公司的平均工资t2，连接到工资表上（这个工资表为了以月计算，所以处理为t1）,连接后的表为t3。
• t3表根据月与部门分类，并通过CASE得出新列。

注意

• 每个月发工资的日期可能不同，DATE_FORMAT 从内层开始就需要添加。
• 小数计算，记得添加ROUND