累计求值-leetcode 579查询员工的累积薪水

Question:
Employee表中有一年的员工薪资信息，写一个sql来获得3个月内员工工资的累计总和，但不包括最近一个月。
原表：

image.png

输出：

image.png

这个题目的关键信息是：在每个月份后面返回3个月的累计总和。
方法一：开窗函数

SELECT
    id,
    MONTH,
    sum( salary ) over ( PARTITION BY id ORDER BY MONTH ASC rows BETWEEN 2 preceding AND current ROW ) salary 
FROM
    employee2 a 
WHERE
    MONTH < ( SELECT max( MONTH ) MONTH FROM employee2 b WHERE a.id = b.id ) 
ORDER BY
    id ASC,
    MONTH DESC;

关于 sum() over(partition by id order by month asc rows between 2 preceding and current row)，是求给定范围内的累计值的开窗函数的固定语法，在简书《开窗函数》中有具体介绍。
方法二：两表进行join，取初符合条件的月份，然后再group by在sum()

SELECT
    a.id,
    a.MONTH,
    sum( b.salary ) salary 
FROM
    employee2 a
    JOIN employee2 b ON a.id = b.id 
    AND a.MONTH - b.MONTH BETWEEN 0 AND 2 
WHERE
    a.MONTH < ( SELECT max( MONTH ) MONTH FROM employee2 b WHERE a.id = b.id ) 
GROUP BY
    a.id,
    a.MONTH 
ORDER BY
    a.id,
    a.MONTH DESC

关键信息是两表join后的and后面的条件的设定，取出每个月的前三个月份，以及对a表进行分组，然后对相应的b表进行group by求和。
还可以进行另一种表示，不同之处在于对最近月份的限制，上面是在where条件里面，两表进行关联后取出最大值，然后使a.month<该最大值，下面是多表连接限制最近的月份。

SELECT
    a.Id,
    a.MONTH,
    sum( b.Salary ) AS salary 
FROM
    employee2 AS a
    JOIN employee2 AS b 
        ON a.Id = b.Id AND b.MONTH BETWEEN a.MONTH - 2 AND a.MONTH 
        JOIN ( SELECT Id, max( MONTH ) AS max_mon FROM employee2 GROUP BY id ) AS c 
        ON a.Id = c.Id 
    AND a.MONTH < c.max_mon 
GROUP BY
    a.Id,
    a.MONTH 
ORDER BY
    id,
        MONTH DESC

方法三：利用变量@解决。。。有点难度。先帖在这儿

select id,month,sum_salary
from 
(select id,month,
       if(@pre_id=id,@prank:=@prank+1,@prank:=1),
    @pre_id:=id,  
      if(@prank=1,null,ifnull(salary1,0)+ifnull(salary2,0)+ifnull(salary3,0)) as sum_salary
from 
(select a.id,a.month,a.salary as salary1,b.salary as salary2,c.salary as salary3
from 
(select id,month,salary from employee2 order by id asc,month desc) a
left join employee2 b on a.id = b.id and a.month = b.month + 1  
left join employee2 c on a.id = c.id and a.month = c.month + 2
order by a.id asc,month desc) c,
(select @prank:=1,@pre_id:=null,@cu_salary:=0) d
order by id asc,month desc) a 
where sum_salary is not null