本文介绍了使用 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;更多推荐
使用 MySQL 在每个组中运行总计
发布评论