列的累计总和不工作

问题描述：

我试图做due_pis列的累加值，使用此：列的累计总和不工作

set @csum := 0; 
select SUM(e.vl_taxe) as vl_taxe, 
SUM(CASE WHEN e.hold_pis = 0 THEN e.due_pis ELSE 0 END) as total, 
(@csum := @csum + SUM(CASE WHEN e.hold_pis = 0 THEN e.due_pis ELSE 0 END)) as cumulative_sum 
FROM taxes as e 
WHERE e.id_enterprise = 1 
AND (e.dt_taxe BETWEEN '2016-12-01' AND '2017-04-01') AND lg_cancel = 0 
GROUP BY e.dt_taxe

结果是：

vl_taxe  total cumulative_sum 

24850.76 1.37  1.37 
31444.64 1.26  1.26 
32329.27  0  0 
33654.82 1.37  1.37 
39682.94  0  0

但是，我想获得这样的：

vl_taxe  total cumulative_sum 

24850.76 1.37  1.37 
31444.64 1.26  2.63 
32329.27  0  2.63 
33654.82 1.37  4 
39682.94  0  4

是否有任何列来指定排序？ –

[累计汇总列]（https://*.com/questions/13862432/accumulate-a-summarized-column）可能的重复 –

答

获取每个日期的合计值，然后指定一个order by以获得所需的cumula五笔。

set @csum := 0; 

select dt_taxe, 
vl_taxe, 
total, 
@csum := @csum + total as cumulative_sum 
from (select e.dt_taxe, 
     SUM(e.vl_taxe) as vl_taxe, 
     SUM(CASE WHEN e.hold_pis = 0 THEN e.due_pis ELSE 0 END) as total 
     FROM taxes as e 
     WHERE e.id_enterprise = 1 
     AND (e.dt_taxe BETWEEN '2016-12-01' AND '2017-04-01') 
     AND e.lg_cancel = 0 
     GROUP BY e.dt_taxe 
    ) t 
ORDER BY dt_taxe

列的累计总和不工作

相关推荐