数据准备
drop table Employee;
Create table If Not Exists Employee (id int, month int, salary int);
Truncate table Employee;
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');
insert into Employee (id, month, salary) values ('1', '7', '90');
insert into Employee (id, month, salary) values ('1', '8', '90');
需求
请你编写 SQL 语句,对于每个员工,查询他除最近一个月(即最大月)之外,剩下每个月的近三个月的累计薪水(不足三个月也要计算)
输入
分析
- t1: 新增一列,以id分组,以id和month(逆序)排序
- t2: 新增一列,以id分组,以id和month(正序)排序,并求出前两行与当前行的salary总和(筛选出最大的那个月的薪水不要)
- 最后求出所需要的数据
输出
/*
t1: 新增一列,以id分组,以id和month(逆序)排序
t2: 新增一列,以id分组,以id和month(正序)排序,
并求出前两行与当前行的salary总和(筛选出最大的那个月的薪水不要)
最后求出所需要的数据
*/
with t1 as (
select *,
row_number() over (partition by id order by id,month desc ) rn1
from Employee
),t2 as (
select *,
sum(salary) over (partition by id order by id,month rows between 2 preceding and current row ) as rn2
from t1
where rn1 !=1
)
select id as Id,
month as Month,
rn2 as Salary
from t2
order by Id,Month desc
;