每日sql--统计员工近三个月的总薪水（不包括最新一个月）

每日sql–统计员工近三个月的总薪水（不包括最新一个月）

DDL

Create table If Not Exists Employee (Id int, Month int, Salary int);insert into Employee (Id, Month, Salary) values (1, 1, 20);insert into Employee (Id, Month, Salary) values (2, 1, 20);insert into Employee (Id, Month, Salary) values (1, 2, 30);insert into Employee (Id, Month, Salary) values (2, 2, 30);insert into Employee (Id, Month, Salary) values (3, 2, 40);insert into Employee (Id, Month, Salary) values (1, 3, 40);insert into Employee (Id, Month, Salary) values (3, 3, 60);insert into Employee (Id, Month, Salary) values (1, 4, 60);insert into Employee (Id, Month, Salary) values (3, 4, 70);

sql

select e1.Id,e1.month, case when  e1.salary is null then  0 else e1.salary end +case when  e2.salary is null then  0 else e2.salary end+case when  e3.salary is null then  0 else e3.salary end AS Salary
from 
(select Id,max(month)as month from Employee group by Id having count(1)>1) maxmonth
left join Employee e1 on e1.Id = maxmonth.Id and maxmonth.month >e1.month
left join Employee e2 on e2.Id = e1.Id and e2.month = e1.month-1
left join Employee e3 on e3.Id = e1.Id and e3.month = e1.month-2
ORDER BY e1.id ASC , e1.month DESC;