使用 MySQL 在每个组中运行总计 [英] Running total with in each group using 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 小提琴:http://sqlfiddle.com/#!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;

这篇关于使用 MySQL 在每个组中运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！