在MySQL中为多个类别运行求和 [英] Running Sums for Multiple Categories in MySQL

问题描述

我有一张表格，形式

 Category      Time      Qty  
     A           1        20 
     B           2         3
     A           3        43
     A           4        20
     B           5        25

我需要一个运行总计来按MySQL中的类别进行计算.结果将如下所示:

I need a running total to be calculated by category in MySQL. The result would look something like this:

 Category      Time      Qty     Cat.Total  
     A           1        20         20
     B           2         3          3
     A           3        43         63
     A           4        20         83
     B           5        25         28

有人知道如何在MySQL中高效地做到这一点吗?我进行了广泛的搜索，但是我所能找到的是关于如何在MySQL中插入单个运行总计的信息.我想知道是否有任何方法可以使用GROUP BY或类似的构造来实现这一目标.

Any idea how I could do this efficiently in MySQL? I have searched far and wide, but all I can find is info on how to insert one single running total in MySQL. I wonder if there's any way to use GROUP BY or a similar construct to achieve this.

推荐答案

您可以在子查询中计算总和:

You could calculate the sum in a subquery:

select  Category
,       Time
,       Qty
,       (
        select  sum(Qty) 
        from    YourTable t2 
        where   t1.Category = t2.Category 
                and t1.Time >= t2.Time
        ) as CatTotal
from    YourTable t1

为了提高速度，提高了可读性，您可以使用MySQL变量来保存运行总和:

Trading readability for speed, you can use a MySQL variable to hold the running sum:

select  Category
,       Time
,       Qty
,       @sum := if(@cat = Category,@sum,0) + Qty as CatTotal
,       @cat := Category
from    YourTable
cross join
        (select @cat := '', @sum := 0) as InitVarsAlias
order by
        Category
,       Time

此构造需要排序；如果需要其他顺序，请将查询包装在子查询中:

The ordering is required for this construct to work; if you need a different order, wrap the query in a subquery:

select  Category
,       Time
,       Qty
,       CatTotal
from    (
        select  Category
        ,       Time
        ,       Qty
        ,       @sum := if(@cat = Category,@sum,0) + Qty as CatTotal
        ,       @cat := Category
        from    YourTable
        cross join
                (select @cat := '', @sum := 0) as InitVarsAlias
        order by
                Category
        ,       Time
        ) as SubQueryAlias
order by
        Time

这篇关于在MySQL中为多个类别运行求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！