两个表,写一个查询语句,求出在每一个工资发放日,每个部门的平均工资与公司的平均工资的比较结果 (高 / 低 / 相同)
分析
- 不同部门,不同时间的平均工资 (时间、部门 分组, 聚合)
- 公司不同时间的平均工资
- 比较,高、低、相同
解题
- 时间格式化
select employee_id,amount,DATE_FORMAT(pay_date,"%Y-%m") as pay_month from salary ;
- 不同部门,不同时间的平均工资 (时间、部门 分组, 聚合)
SELECT
AVG(amount) as avg,a.pay_month,b.department_id
FROM
(select employee_id,amount,DATE_FORMAT(pay_date,"%Y-%m") as pay_month from salary) a
LEFT JOIN employee b ON a.employee_id = b.employee_id
GROUP BY a.pay_month DESC,b.department_id;
- 公司不同时间的平均值
select AVG(a.amount) as avg,a.pay_month from (select employee_id,amount,DATE_FORMAT(pay_date,"%Y-%m") as pay_month from salary) a GROUP BY a.pay_month;
- 比较
select a.pay_month,a.department_id,
case
WHEN a.avg > b.avg then "higher"
WHEN a.avg = b.avg then "same"
ELSE "lower"
END AS comparison
from (
SELECT
AVG(amount) as avg,a.pay_month,b.department_id
FROM
(select employee_id,amount,DATE_FORMAT(pay_date,"%Y-%m") as pay_month from salary) a
LEFT JOIN employee b ON a.employee_id = b.employee_id
GROUP BY a.pay_month DESC,b.department_id) a
left JOIN (select AVG(a.amount) as avg,a.pay_month from (select employee_id,amount,DATE_FORMAT(pay_date,"%Y-%m") as pay_month from salary) a GROUP BY a.pay_month) b
on a.pay_month = b.pay_month
注意点
- case when 的用法
- DATE_FORMAT(date,format) 函数的使用
- GROUP BY 多个列分组
网友评论