|
|
我试图在下面的输入中写一个SQL计算每组的运行总数。我只想知道如何使用它MySQL。我知道如何使用常规的解析函数SQL但是在MySQL没有。你能分享如何实施它的想法吗？
4 V8 [2 P+ l( W% B& sSQL小提琴：http://sqlfiddle.com/#!9/59366d/19 6 Y8 ^- q; Q" C$ E
使用窗口函数SQL：- j2 e1 X& C, F& |
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.IdWHERE e.Month != emax.maxmonthORDER BY e.Id,e.Month DESC;输入 ：
6 f8 ?& `- v1 NCreate table Employee (Id int,Month int,Salary int);insert into Employee (Id,Month,Salary) values insert into Employee (Id,Month,Salary) values insert into Employee (Id,Month,Salary) values insert into Employee (Id,Month,Salary) values insert into Employee (Id,Month,Salary) values insert into Employee (Id,Month,Salary) values ('1',三，四十；insert into Employee (Id,Month,Salary) values (insert into Employee (Id,Month,Salary) values insert into Employee (Id,Month,Salary) values (输出 ：
8 x2 e6 P$ ^( h- @+ Y& N; J| Id | Month | Salary ||----|-------|--------|| 1 | 3 | || 1 | 2 | 50 || 1 | 1 | || 2 | 1 | || 3 | 3 | 100 || 3 | 2 | 40 | . }$ f6 W+ a1 l2 F: }* S
解决方案: |
|
技术问答
206 人阅读
|
0 人回复
|
2023-09-12
