本文共 1672 字，大约阅读时间需要 5 分钟。

题目链接：

题目大意：略。

解题思路：略。

AC 代码

-- 解决方案(1)select Id, AccMonth as Month, sum(Salary) as Salaryfrom(    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 accgroup by Id, AccMonthorder by Id, Month desc;-- 解决方案(2)SELECT    E1.id,    E1.month,    (IFNULL(E1.salary, 0) + IFNULL(E2.salary, 0) + IFNULL(E3.salary, 0)) AS SalaryFROM    (SELECT        id, MAX(month) AS month    FROM        Employee    GROUP BY id    HAVING COUNT(*) > 1) AS maxmonth        LEFT JOIN    Employee E1 ON (maxmonth.id = E1.id        AND maxmonth.month > E1.month)        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 id ASC , month DESC;-- 解决方案(3)WITH t1 AS(    SELECT e1.Id Id1, e1.Month Month1, e2.Month Month2, e3.Month Month3, IFNULL(e1.Salary, 0) + IFNULL(e2.Salary, 0) + IFNULL(e3.Salary, 0) Salary    FROM Employee e1    LEFT JOIN Employee e2 ON e1.Id = e2.Id AND e1.Month = e2.Month + 1    LEFT JOIN Employee e3 ON e2.Id = e3.Id AND e2.Month = e3.Month + 1),t2 AS(SELECT Id1, Month1, Month2, Month3, Salary, ROW_NUMBER() OVER(PARTITION BY Id1 ORDER BY Id1, IFNULL(Month1, IFNULL(Month2, Month3)) DESC) rkFROM t1)SELECT Id1 id, Month1 month, SalaryFROM t2WHERE rk <> 1

转载地址：https://lux-sun.blog.csdn.net/article/details/117771615 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！