首先对于这道题的题目意思要理解清楚:不是说求除最大月之外近三个月的累计薪水;而是求每个月的近三个月的累计薪水,不足三个月也要计算。
方法一:
第一步:去掉每个员工,最近一个月的信息
-- with t as -- ( -- select Employee.Id,Employee.Month,Employee.Salary -- from Employee -- inner join -- ( -- select Id,max(Month) max_month -- from Employee -- group by Id -- ) m -- on Employee.Id=m.Id and -- Employee.Month != m.max_month -- )
第二步:使用左连接
-- select a.Id,a.Month,a.salary+ifnull(b.salary,0)+ifnull(c.salary,0) salary -- from t a -- left join t b -- on a.Id=b.Id and a.Month-b.Month=1 -- left join t c -- on a.Id=c.Id and a.Month-c.Month=2 -- order by Id,Month desc;
方法二:
第一步:去掉每个员工,最近一个月的信息
select id,month,salary,rank() over(partition by id order by month desc) as rn from Employee;
第二步:
select id,month,sum(salary) over(partition by id order by month rows between 2 PRECEDING and CURRENT ROW ) as salary from ( select id,month,salary,rank() over(partition by id order by month desc) as rn from Employee ) t where rn > 1 order by id asc,month desc;
这里使用了row between 2 PRECEDING and CURRENT ROW
#题目意思理解错误 -- select Id,Month, -- sum(Salary) over (partition by Id order by Month) salary -- from -- ( -- select Employee.Id,Employee.`Month`,Employee.Salary,a.mM-Employee.Month diffmonth -- from Employee -- left join -- ( -- select Id,max(Month) mM -- from Employee -- group by Id -- ) a -- on Employee.Id=a.Id -- ) b -- where b.diffmonth in (1,2,3) -- order by Id,Month desc;