大数据第61天—MySQL之员工累计薪水-杨大伟
需求：查询一个员工三个月内的累计薪水，但是不包括最近一个月的薪水。
展示效果：
| Id | Month | Salary |
|---|---|---|
| 1 | 3 | 90 |
| 1 | 2 | 50 |
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 3 | 3 | 100 |
| 3 | 2 | 40 |
1 Create table If Not Exists 19_Employee (Id int, Month int, Salary int); 2 Truncate table 19_Employee; 3 insert into 19_Employee (Id, Month, Salary) values (1, 1, 20); 4 insert into 19_Employee (Id, Month, Salary) values (2, 1, 20); 5 insert into 19_Employee (Id, Month, Salary) values (1, 2, 30); 6 insert into 19_Employee (Id, Month, Salary) values (2, 2, 30); 7 insert into 19_Employee (Id, Month, Salary) values (3, 2, 40); 8 insert into 19_Employee (Id, Month, Salary) values (1, 3, 40); 9 insert into 19_Employee (Id, Month, Salary) values (3, 3, 60); 10 insert into 19_Employee (Id, Month, Salary) values (1, 4, 60); 11 insert into 19_Employee (Id, Month, Salary) values (3, 4, 70);
说明：员工 1 除去最近一个月（月份 4），有三个月的薪水记录：月份 3 薪水为 40，月份 2 薪水为 30，月份 1 薪水为 20。所以近 3 个月的薪水累计分别为 (40 + 30 + 20) = 90，(30 + 20) = 50 和 20。
最终SQL:
1 SELECT 2 E1.id, 3 E1.month, 4 (IFNULL(E1.salary, 0) + IFNULL(E2.salary, 0) + IFNULL(E3.salary, 0)) AS Salary 5 FROM 6 (SELECT 7 id, MAX(month) AS month 8 FROM 9 19_Employee 10 GROUP BY 11 id 12 HAVING 13 COUNT(*) > 1) AS maxmonth 14 LEFT JOIN 15 19_Employee E1 16 ON 17 (maxmonth.id = E1.id AND maxmonth.month > E1.month) 18 LEFT JOIN 19 19_Employee E2 20 ON 21 (E2.id = E1.id AND E2.month = E1.month - 1) 22 LEFT JOIN 23 19_Employee E3 24 ON 25 (E3.id = E1.id AND E3.month = E1.month - 2) 26 ORDER BY 27 id ASC , month DESC;
