Mysql选择求和，但最后三条记录

Question

我有一个包含字段"客户日期"one_answers"金额"的表我想对按客户分组的金额求和，每个客户按日期的最后两个金额除外样本数据

customer     date               amount
a           2020-10-1             100
a           2020-10-2             150
a           2020-10-3             30
a           2020-10-4             20
b           2020-10-1             1
b           2020-10-5             13
b           2020-10-7             50
b           2020-10-9             18

期望结果

Customer    Amount
A          150
B           14

类似的东西

select Customer , 
SUM(amount- last 2 amount)
From TableA
Group By Customer

Answer 1

一个选项使用窗口函数，在MySQL 8.0中可用：

select customer, sum(amount) total_amount
from (
select a.*, row_number() over(partition by customer order by date desc) rn
from tablea a
) a
where rn > 2
group by customer

在早期版本中，一种替代方案使用相关子查询，该子查询返回每个客户的第三个最新日期进行筛选：

select customer, sum(amount) total_amount
from tablea a
where date <= (select a1.date from tablea a1 where a1.customer = a.customer order by a1.date desc limit 2, 1)
group by customer