Leetcode_615. 平均工资:部门与公司比较

题目难度

困难

题目描述

给如下两个表,写一个查询语句,求出在每一个工资发放日,每个部门的平均工资与公司的平均工资的比较结果 (高 / 低 / 相同)。
表: 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
上一篇:Feign使用和使用


下一篇:如何在Mac上设置和使用Apple Pay