cte 题目如下：
以下为原文
drop table salary
Create table salary(Id int,employee_id int,amount int,pay_date date);
insert into salary values(1,1,9000,'2017-03-31'); insert into salary values(2,2,6000,'2017-03-31'); insert into salary values(3,3,10000,'2017-03-31'); insert into salary values(4,1,7000,'2017-02-28'); insert into salary values(5,2,6000,'2017-02-28'); insert into salary values(6,3,8000,'2017-02-28');
drop table employee Create table employee(Id int,department_id int);
insert into employee values(1,1); insert into employee values(2,2); insert into employee values(3,2);
Given two tables as above, write a query to display the comparison result (higher/lower/same) of the average salary of employees in a department to the company's average salary.
So for the sample data above, the result is:
| pay_month | department_id | comparison | |-----------|---------------|-------------| | 2017-03 | 1 | higher | | 2017-03 | 2 | lower | | 2017-02 | 1 | same | | 2017-02 | 2 | same | with cte1 as( select FORMAT(pay_date, 'yyyy-MM') as Pay_Month,avg(amount) as comp_pay from salary group by FORMAT(pay_date, 'yyyy-MM') ),cte2 as( select e.department_id,FORMAT(pay_date, 'yyyy-MM') as Pay_Month,AVG(s.amount) as dept_pay from salary s join employee e on s.employee_id=e.Id group by e.department_id,FORMAT(pay_date, 'yyyy-MM') ) select cte1.Pay_Month, cte2.department_id, case when cte2.dept_pay > cte1.comp_pay then 'higher' when cte2.dept_pay < cte1.comp_pay then 'lower' else 'Same' end as comparison from cte1 join cte2 on cte1.Pay_Month=cte2.Pay_Month order by Pay_Month desc,cte2.department_id