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

题目
Employee 表保存了一年内的薪水信息。

请你编写 SQL 语句，来查询一个员工三个月内的累计薪水，但是不包括最近一个月的薪水。

结果请按 'Id' 升序，然后按 'Month' 降序显示。

示例：
输入：

输出：

解释：

员工 '1' 除去最近一个月（月份 '4'），有三个月的薪水记录：月份 '3' 薪水为 40，月份 '2' 薪水为 30，月份 '1' 薪水为 20。

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

生成数据

CREATE TABLE employees5(Id INT,`Month` INT,salary INT);

INSERT INTO employees5 VALUES(1,1,20);
INSERT INTO employees5 VALUES(2,1,20);
INSERT INTO employees5 VALUES(1,2,30);
INSERT INTO employees5 VALUES(2,2,30);
INSERT INTO employees5 VALUES(3,2,40);
INSERT INTO employees5 VALUES(1,3,40);
INSERT INTO employees5 VALUES(3,3,60);
INSERT INTO employees5 VALUES(1,4,60);
INSERT INTO employees5 VALUES(3,4,70);

解答

-- 先去掉每个员工最近一个月(每个员工的最近一个月可能是不同的)的数据 再按id和月份排序
SELECT E.`Id`, MAX(E.`Month`) AS  max_month
FROM employees5 AS E
GROUP BY E.`Id`;

SELECT *
FROM employees5 AS EE
WHERE (EE.`Id`, EE.`Month`) NOT IN (SELECT E.`Id`, MAX(E.`Month`) AS  max_month
FROM employees5 AS E
GROUP BY E.`Id`)
GROUP BY EE.`Id`, EE.`Month`


-- 错误的尝试
SELECT *,
IF(E.`Id` = @pre_id AND E.`Month` = @pre_month + 1, E.`salary` + @pre_salary, E.`salary`) AS ACCsalary,
@pre_id:= E.`Id`,
@pre_month:=E.`Month`,
@pre_salary:=E.`salary`
FROM employees5 AS E, (SELECT @pre_id:=NULL, @pre_month:=NULL, @pre_salary:=0) AS init
WHERE E.`Month` <> (SELECT MAX(`Month`) FROM employees5)
ORDER BY E.`Id`, E.`Month`;

-- 引入变量 pre_id来判断id是否变化 acc来表示积累的薪资
SELECT E.Id, E.month,
IF(E.`Id` = @pre_id, @acc:=@acc + E.`salary`, @acc:=E.`salary`) AS ACCsalary,
@pre_id:= E.`Id`
FROM (SELECT *
FROM employees5 AS EE
WHERE (EE.`Id`, EE.`Month`) NOT IN (SELECT E.`Id`, MAX(E.`Month`) AS  max_month
FROM employees5 AS E
GROUP BY E.`Id`)
GROUP BY EE.`Id`, EE.`Month`) AS E, 
(SELECT @pre_id:=NULL, @acc:=0) AS init;

-- 子查询选出想要的字段 然后进行排序即可
SELECT tmp.Id, tmp.Month, tmp.ACCsalary AS Salary
FROM (SELECT E.Id, E.month,
IF(E.`Id` = @pre_id, @acc:=@acc + E.`salary`, @acc:=E.`salary`) AS ACCsalary,
@pre_id:= E.`Id`
FROM (SELECT *
FROM employees5 AS EE
WHERE (EE.`Id`, EE.`Month`) NOT IN (SELECT E.`Id`, MAX(E.`Month`) AS  max_month
FROM employees5 AS E
GROUP BY E.`Id`)
GROUP BY EE.`Id`, EE.`Month`) AS E, 
(SELECT @pre_id:=NULL, @acc:=0) AS init
) AS tmp
ORDER BY tmp.Id, tmp.Month DESC