MySQL使用重置条件计算累计和 [英] MySQL calculation of cumulative sum with a reset condition

问题描述

在mySQL中是否有一种方法可以捕获具有重置条件的累积总和?用例是一个简单的收银台表

Is there a way to capture a cummulative sum with a reset condition in mySQL? The use case for this is a simple cash register table

id    transactionType      value  CurrentBalance
1      purchase             10        10
2      sale                -10        0
3      RESET                20        20
4      purchase             10        30
5      sale                -10        20
6      sale                 10        30
7      sale                -20       10

这里的答案是一个很好的起点，但我不知道如何扩展它: 在MySQL中创建累积总和列

The answer from here is a good starting point but I don't see how to extend it: Create a Cumulative Sum Column in MySQL

SELECT t.id,
         t.count,
         @running_total := @running_total + t.value AS CurrentBalance
    FROM TABLE t
    JOIN (SELECT @running_total := 0) r
ORDER BY t.id

注意:基本上，这个想法是每次重设时都会重设累积总和.理想情况下，我尝试使用on更新/插入触发器，该触发器将考虑RESET来更新整个CurrentBalance列.这是一个小表，所以我不介意更新整个表以换取简单的内容.

NOTE: basically the idea is to reset the cumulative sum each time a reset is hit. Ideally I am trying to have an on update/insert trigger which will update the entire CurrentBalance column taking into account RESETs. This is a small table so I don't mind updating the whole table in exchange for something simple.

推荐答案

所有这需要的是一些简单的条件逻辑:

All this requires is some simple conditional logic:

SELECT t.id, t.count,
       @running_total := if(transactionType = 'RESET', t.value,
                            @running_total + t.value
                           ) as CurrentBalance
FROM TABLE t JOIN
     (SELECT @running_total := 0) params
ORDER BY t.id;

这篇关于MySQL使用重置条件计算累计和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！