【力扣题库-SQL-备忘录579_查询员工的累计薪水】_力扣579

今日力扣训练备忘录

579. 查询员工的累计薪水（困难）

Employee 表保存了一年内的薪水信息。请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。结果请按 Id 升序，然后按 Month 降序显示。

#输入：
| Id | Month | Salary |
|----|-------|--------|
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 1  | 2     | 30     |
| 2  | 2     | 30     |
| 3  | 2     | 40     |
| 1  | 3     | 40     |
| 3  | 3     | 60     |
| 1  | 4     | 60     |
| 3  | 4     | 70     |

#输出：
| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 3  | 3     | 100    |
| 3  | 2     | 40     |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

结果示例解释：

#员工 '1'
| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |

#员工 '2'
| Id | Month | Salary |
|----|-------|--------|
| 2  | 1     | 20     |

#员工 '3'
| Id | Month | Salary |
|----|-------|--------|
| 3  | 3     | 100    |
| 3  | 2     | 40     |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

由于题目未说明，同时可能存在非连续性月份薪资信息

#输入：
| Id | Month | Salary |
|----|-------|--------|
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 1  | 2     | 30     |
| 2  | 2     | 30     |
| 3  | 2     | 40     |
| 1  | 3     | 40     |
| 3  | 3     | 60     |
| 1  | 4     | 60     |
| 3  | 4     | 70     |
| 1  | 7     | 90     |
| 1  | 8     | 90     |

#输出
| 1  | 7     | 90     |
| 1  | 4     | 130    |
| 1  | 2     | 50     |
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 3  | 3     | 100    |
| 3  | 2     | 40     |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

首先我们需要去除当前月（最大月份）的薪资，可以有两种方式
方式1

select t.id, t.month, t.salary from (
select *
  ,row_number() over(partition by e.id order by e.month desc) id_month_seq
  from employee e
) t where t.id_month_seq != 1 ;
1
2
3
4
5

方式2

select e.id, e.month, e.salary from employee
  where (id, month) not in (
      select e1.id, max(e1.month) from employee e1 group by e1.id
  )
1
2
3
4

去除了当前月（最大月份）的薪资后，需要对每个月的最近3个月的薪资进行分别求和
方式1 通过表内关联（官方解答）

select Id, AccMonth as Month, sum(Salary) as Salary
from
(
    select a.Id as Id, a.Month as AccMonth, b.Month as Month, b.Salary as Salary
    from 
    (
        select Employee.Id as Id, Employee.Month as Month
        from Employee, (select Id, max(Month) as Month
            from Employee
            group by Id) as LastMonth
            where Employee.Id = LastMonth.Id and Employee.Month != LastMonth.Month) as a 
    join Employee as b
    --关联条件
    on a.Id = b.Id and a.Month - b.Month <= 2 and a.Month - b.Month >= 0
) as acc
group by Id, AccMonth
order by Id, Month desc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

方式2 窗口函数

select id, month
  --指定窗口
  ,sum(salary) over (partition by id order by month range 2 preceding) as Salary 
  from employee 
  where (id,month) not in (select id, max(month) from employee group by id)
  order by id, month desc
1
2
3
4
5
6

备忘录：

WINDOW FUNCTION 滑动窗口函数
RANGE 和 ROW的区别：
RANGE 逻辑上的排序，如果有缺失月份，也会被考虑进去;
ROW 按照实际表格的排序，也就是只根据列的位置来决定。
示例中出现非连续月份，所以需要RANGE
PRECEDING 和 FOLLOWING的区别：
PRECEDING 取当前行以及往上的数量行；
FOLLOWING 取向下数量行。
根据题意按照逻辑上的月份倒序排，所以需要用PRECEDING