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 then0else e1.salary end +case when e2.salary is null then0else e2.salary end+case when e3.salary is null then0else 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;
每日sql–统计员工近三个月的总薪水(不包括最新一个月)DDLCreate 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, Sala