当前位置:网站首页>每日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;
边栏推荐
猜你喜欢
随机推荐
HCIP experiments (pap, chap, HDLC, MGRE, RIP)
查看内核版本和发行版版本
pytorch下tensorboard可视化深坑
OA项目之我的审批(查询&会议签字)
【LeetCode】2034. 股票价格波动(思路+题解)双map
HCIP-Spanning Tree (802.1D, Standard Spanning Tree/802.1W: RSTP Rapid Spanning Tree/802.1S: MST Multiple Spanning Tree)
抖音API接口
Map Reduce
HCIP WPN实验
HCIP MPLS/BGP综合实验
Top20括号匹配
Open Set Domain Adaptation 开集领域适应
什么是Inductive learning和Transductive learning
CLUSTER DAY03 (Ceph overview, the deployment of Ceph CLUSTER, Ceph block storage)
uboot sets the default bootdelay
利用opencv读取图片,重命名。
空间点模式方法_一阶效应和二阶效应
Monte Carlo
MySQL之CRUD
自定义MVC增删改查