LeetCode 579.查询员工的累计薪水_579. 查询员工的累计薪水

数据准备

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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

需求

请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）

输入

分析

1. t1: 新增一列,以id分组,以id和month(逆序)排序
2. t2: 新增一列,以id分组,以id和month(正序)排序,并求出前两行与当前行的salary总和(筛选出最大的那个月的薪水不要)
3. 最后求出所需要的数据

输出

/*
 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
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22