SQL突击战——查询员工的累计薪水

579. 查询员工的累计薪水

#SQL架构
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')

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’ 除去最近一个月（月份 ‘4’），有三个月的薪水记录：月份 ‘3’ 薪水为 40，月份 ‘2’ 薪水为 30，月份 ‘1’ 薪水为 20。

所以近 3 个月的薪水累计分别为 (40 + 30 + 20) = 90，(30 + 20) = 50 和 20。

| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |

员工 ‘2’ 除去最近的一个月（月份 ‘2’）的话，只有月份 ‘1’ 这一个月的薪水记录。

| Id | Month | Salary |
|----|-------|--------|
| 2  | 1     | 20     |

员工 ‘3’ 除去最近一个月（月份 ‘4’）后有两个月，分别为：月份 ‘3’ 薪水为 60 和月份 ‘2’ 薪水为 40。所以各月的累计情况如下：

| Id | Month | Salary |
|----|-------|--------|
| 3  | 3     | 100    |
| 3  | 2     | 40     |

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/find-cumulative-salary-of-an-employee
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

题解

方法一：自连接

SELECT *
FROM
    Employee E1
        LEFT JOIN
    Employee E2 ON (E2.id = E1.id
        AND E2.month = E1.month - 1)
ORDER BY E1.id ASC , E1. month DESC

这样就将某个id的month和month-1的数据放在了一行，两列求和就得到了两月累加的薪水。
题目要求累计三个月的，因此在这个的基础上再进行一次自连接

SELECT *
FROM
    Employee E1
        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

方法二：滑窗法

设置一个月份数值向preceding方向取2个的滑窗求和。

select tmp2.Id,tmp2.month,
sum(tmp2.Salary) over (partition by tmp2.id order by tmp2.Month asc range 2 preceding) Salary 
from Employee tmp2 
where (tmp2.id,tmp2.month) not in (
    select id,max(month) from Employee group by id
) order by  tmp2.id asc , tmp2.month desc

难点在语法。

语法笔记

over()中range 和 rows

range 和 rows 的区别在于range是对于数值上的窗口，rows则是实际表中行的窗口。

unbounded following preceding current between

unbounded：无界限
preceding：从分区第一行头开始，则为 unbounded。 N为：相对当前行向前的偏移量
following ：与preceding相反,到该分区结束，则为 unbounded。N为：相对当前行向后的偏移量
current row：当前行，偏移量为0
between … and … 可以进行范围筛选，如

between unbounded preceding and current row #即当前行往前的所有行
between 1 preceding and 1 following #当期行往前1行至后1行的所有行