php - 如何获得上一年的总数与下一年的总数

This question already has answers here:

Calculate a running total in MySQL
                                
                                    （8个答案）
                                
                        
                                5年前关闭。
            
                    
我有一个下面的查询

SELECT COUNT( DISTINCT id ) AS `total` , YEAR( created_date ) AS `memberYear`
FROM `users` AS `u`
WHERE (
role_id =2
AND is_deleted =0
AND is_trashed =0
)
GROUP BY `memberYear`

通过此查询，我正在获得这样的记录

total   memberYear
 10        2012
 12        2013
 2         2014

现在我需要获得上一年的总记录

total   memberYear sumTotal
10        2012       10
12        2013       22
2         2014       24

我如何获得总计的总和？任何帮助将不胜感激。

最佳答案

请尝试以下一项：

SELECT COUNT( DISTINCT id ) AS `total` , YEAR( created_date ) AS `memberYear`,
@running_total := @running_total + COUNT(DISTINCT id) AS total
FROM `users` AS `u`
, (SELECT @running_total := 0) var_init_subquery
WHERE (
role_id =2
AND is_deleted =0
AND is_trashed =0
)
GROUP BY `memberYear`

关于php - 如何获得上一年的总数与下一年的总数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25973930/