当前位置:网站首页>Daily sql--statistics the total salary of employees in the past three months (excluding the latest month)
Daily sql--statistics the total salary of employees in the past three months (excluding the latest month)
2022-08-11 07:14:00 【Eating too much sugar will not gain weight】
每日sql–Calculate the total salary of employees in the past three months(Excluding the latest month)
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;
边栏推荐
猜你喜欢
随机推荐
八股文之mysql
八股文之redis
Implement general-purpose, high-performance sorting and quicksort optimizations
LabelEncoder和LabelBinarizer的区别
皮质-皮质网络的多尺度交流
Pinduoduo api interface application example
HCIA实验
HCIP MPLS/BGP Comprehensive Experiment
Eight-legged text of mysql
TOP2 Add two numbers
抖音API接口
获取拼多多商品信息操作详情
什么是Inductive learning和Transductive learning
概念名词解释
arcgis填坑_1
阿里巴巴规范之POJO类中布尔类型的变量都不要加is前缀详解
arcgis填坑_4
知识蒸馏Knownledge Distillation
每日sql-员工奖金过滤和回答率排序第一
The ramdisk practice 1: the root file system integrated into the kernel