MySQL计算月份之间的差异

Question

我有以下代码：

select DATE_FORMAT(`Import_Date`, '%M %Y') as Month, 
       concat('R ', FORMAT(SUM(Total_Amount),2)) as Total_Amount 
from `overtime` 
group by `Import_Date` 
order by EXTRACT(YEAR_MONTH FROM `Import_Date`);

生成以下代码：

+---------------+----------------+
| Month         | Total_Amount   |
+---------------+----------------+
| November 2015 | R 4,649,211.48 |
| December 2015 | R 4,470,413.37 |
| January 2016  | R 4,504,793.31 |
| February 2016 | R 3,143,027.89 |
| March 2016    | R 2,026,576.65 |
| April 2016    | R 3,632,761.58 |
+---------------+----------------+

我如何计算两行之间的差异，作为一个单独的列，称为Diff...？

Answer 1

试一试

select DATE_FORMAT(`Import_Date`, '%M %Y') as Month, 
       concat('R ', FORMAT(SUM(Total_Amount),2)) as Total_Amount,
       SUM(Total_Amount) - @previous as diff,
       @previous := SUM(Total_Amount)
from `overtime` 
cross join (select @previous := 0) p
group by `Import_Date` 
order by EXTRACT(YEAR_MONTH FROM `Import_Date`);