使用 MySQL 在每个组中运行总计

本文介绍了使用 MySQL 在每个组中运行总计的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试编写一个 SQL 来计算以下输入中每个组的运行总数.只是想知道如何使用 MySQL 来做到这一点.我知道如何使用分析函数在常规 SQL 中执行此操作，但在 MySQL 中不知道.您能否分享您对如何实施它的想法.

I am trying to write a SQL to calculate running total with in each group in the below input. Just wondering how can I do it using MySQL. I am aware of how to do it in regular SQL using analytic functions but not in MySQL. Could you share your thoughts on how to implement it.

SQL 小提琴:sqlfiddle/#!9/59366d/19

使用窗口函数的SQL:

SQL using window function :

SELECT e.Id, SUM( e.Salary ) OVER( PARTITION BY e.Id ORDER BY e.Month ) AS cumm_sal FROM Employee e LEFT JOIN ( SELECT Id,MAX(Month) AS maxmonth FROM Employee GROUP BY Id ) emax ON e.Id = emax.Id WHERE e.Month != emax.maxmonth ORDER BY e.Id,e.Month DESC;

输入:

Create table Employee (Id int, Month int, Salary int); insert into Employee (Id, Month, Salary) values ('1', '1', '20'); insert into Employee (Id, Month, Salary) values ('2', '1', '20'); insert into Employee (Id, Month, Salary) values ('1', '2', '30'); insert into Employee (Id, Month, Salary) values ('2', '2', '30'); insert into Employee (Id, Month, Salary) values ('3', '2', '40'); insert into Employee (Id, Month, Salary) values ('1', '3', '40'); insert into Employee (Id, Month, Salary) values ('3', '3', '60'); insert into Employee (Id, Month, Salary) values ('1', '4', '60'); insert into Employee (Id, Month, Salary) values ('3', '4', '70');

输出:

| Id | Month | Salary | |----|-------|--------| | 1 | 3 | 90 | | 1 | 2 | 50 | | 1 | 1 | 20 | | 2 | 1 | 20 | | 3 | 3 | 100 | | 3 | 2 | 40 |

推荐答案

在 MySQL 中，最有效的方法是使用变量:

In MySQL, the most efficient approach is to use variables:

select e.*, (@s := if(@id = e.id, @s + salary, if(@id := e.id, salary, salary) ) ) as running_salary from (select e.* from employee e order by e.id, e.month ) e cross join (select @id := -1, @s := 0) params;

您也可以使用相关子查询来执行此操作:

You can also do this with a correlated subquery:

select e.*, (select sum(e2.salary) from employee e2 where e2.id = e.id and e2.month <= e.month ) as running_salary from employee e;