当前位置:网站首页>每日sql--统计员工近三个月的总薪水(不包括最新一个月)

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

2022-08-11 05:36:00 吃再多糖也不长胖

每日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;

在这里插入图片描述

原网站

版权声明
本文为[吃再多糖也不长胖]所创,转载请带上原文链接,感谢
https://blog.csdn.net/weixin_43859562/article/details/122714049