SQL实战练习题(6)-根据平均薪资将部门划分级别-leetcode sql 615

目录

• 问题描述
• 数据SQL
• 答案

问题描述

数据SQL

create table salary (
	id int primary key auto_increment,
	employee_id int,
	amount int,
	pay_date date
);

create table employee (
	employee_id int primary key auto_increment,
	department_id int
);

insert
	into
	salary (employee_id,
	amount,
	pay_date)
values
	(1, 9000, '2017-03-31'),
	(2, 6000, '2017-03-31'),
	(3, 10000, '2017-03-31'),
	(1, 7000, '2017-02-28'),
	(2, 6000, '2017-02-28'),
	(3, 8000, '2017-02-28');

insert
	into
	employee (employee_id,
	department_id)
values
	(1,1),
	(2,2),
	(3,2);

答案

with
department_avg_salay as(
	select
		DATE_FORMAT(pay_date, '%Y-%m') as pay_month,
		tbl2.department_id,
		avg(amount) department_avg_month_amount   -- 部门每月平均工资
	from
		salary tbl1
	join employee tbl2 on
		tbl1.employee_id = tbl2.employee_id
	group by pay_date, tbl2.department_id
)
,company_avg_salay as(
	select
		DATE_FORMAT(pay_date, '%Y-%m') as pay_month,
		round(avg(amount)) company_avg_month_amount   -- 公司每月平均工资
	from
		salary tbl1
	group by DATE_FORMAT(pay_date, '%Y-%m')
)
SELECT 
	tb1.pay_month, 
	tb1.department_id, 
	tb1.department_avg_month_amount, 
	tb2.company_avg_month_amount,
	case when tb1.department_avg_month_amount > tb2.company_avg_month_amount then 'h'
		  when tb1.department_avg_month_amount = tb2.company_avg_month_amount then 's'
		  else 'l'
	end comparison
FROM department_avg_salay tb1 
	join company_avg_salay tb2 on tb1.pay_month = tb2.pay_month
;