MYSQL 按另一列选择一列组

Question

我有如下数据的表格:

我想得到以下输出:

其实我想做这样的事情:

select CustomerName, (select sum(Amount) from tbl where tbl.CustomerName = CustomerName) as Amount, consumedDate from tbl

但是由于数据是使用子查询获取的，所以我无法在select语句中使用子查询来选择金额:

select CustomerName, (select sum(Amount) from myTable where myTable.CustomerName = tbl.CustomerName) as Amount, consumedDate from (select CustomerName, Amount, ConsumedDate from myTable) as tbl

上面的查询会报错:

Table tbl doesn't exist

有解决办法吗？

Answer 1

我建议为此目的使用变量。这需要两次通过:

select customername, consumeddate,
       (@a := if(@c = customername, @a,
                 if(@a := customername, cume_amount, cume_amount)
                )
       ) amount
from (select t.*,
             (@a := if(@c = customername, @a + amount,
                       if(@c := customername, amount, amount)
                      )
             ) as cume_amount
      from (<your subquery here>) t cross join
           (select @c := '', @a := 0) params
      order by customername
     ) t cross join
     (@ac := '', @a := 0) params
order by customername, cume_amount desc;

第一遍计算每个客户的累计总和。第二个将最大值复制回客户的所有行。