如何获得“运行总和”？或者在SQL中运行团队总分？ [英] How do I get a "running sum" or running total of team scores in SQL?

问题描述

您好

以下代码适用于为每个4人的团队提供每周分数。



如何运行总计？



尝试过几种方法但没有成功。





< b>编辑：对于关注此主题的人，这里有一些测试数据：

Hello
The below code works for giving me weekly scores for a team of 4 players.

How do I get a running total?

Have tried few ways without success.


for anyone following this thread, here is some test data:

(
   select
       '01' as Week,
       4 as TeamNum,
       1 as GameNum,
       684 as Gm1,
       810 as Gm2,
       659 as Gm3
   union select
       '02' as Week,
       4 as TeamNum,
       2 as GameNum,
       667 as Gm1,
       810 as Gm2,
       659 as Gm3
   union select
       '03' as Week,
       4 as TeamNum,
       3 as GameNum,
       506 as Gm1,
       810 as Gm2,
       659 as Gm3
   union select
       '04' as Week,
       4 as TeamNum,
       4 as GameNum,
       668 as Gm1,
       810 as Gm2,
       659 as Gm3
   union select
       '05' as Week,
       4 as TeamNum,
       5 as GameNum,
       688 as Gm1,
       810 as Gm2,
       659 as Gm3) as testData

很抱歉混淆，但之前的答案部分工作，直到今天才注意到。

它给出了一个总计的结果，但没有在团队之间分开。

我试过不同版本的分组周或团队不起作用br />
感谢您的解决方案，但我会提供一些实际的样本数据和预期的结果以供澄清。



1.

Sorry for confusion but previous answer worked partially and did not notice until today.
It gave the result of a running total but did not separate between teams.
When I tried different versions of grouping by "week" or "team" did not work.
Thanks for solutions but I will give some actual sample data and the expected result for clarifications.

1.

table b_Scores
Week	TeamNum	Gm1	Gm2	Gm3	Total	Won	Lost
01	1	283	263	323	  869	2	1
01	2	798	768	703	2,269	1	2
01	3	599	701	580	1,880	2	1
02	1	446	384	456	1,286	0	3
02	2	748	811	738	2,297	1	2
02	3	550	551	491	1,592	3	0
03	1	405	380	395	1,180	3	0
03	2	683	787	803	2,273	0	3
03	3	564	694	604	1,862	3	0

2.

2.

table b_Matches
MatchWk	MatchTm	WON	LOST
1	1	2	1
1	2	1	2
1	3	2	1
2	1	0	3
2	2	1	2
2	3	3	0
3	1	3	0
3	2	0	3
3	3	3	0

3.预期结果。

3. Expected Result.

RESULT EXPECTED
Week	Team  Num	Gm1	Gm2	Gm3	Total	Rtotal	Won	Lost	Total  Wins	Total Losses
1	1	283	263	323	  869	  869	2	1	2	1
1	2	798	768	703	2,269	3,138	1	2	3	3
1	3	599	701	580	1,880	5,018	2	1	5	4
2	1	446	384	456	1,286	1286	0	3	0	3
2	2	748	811	738	2,297	3,583	1	2	1	5
2	3	550	551	491	1,592	5,175	3	0	4	5
3	1	405	380	395	1,180	1180	3	0	3	0
3	2	683	787	803	2,273	3,453	0	3	3	3
3	3	564	694	604	1,862	5,315	3	0	6	3

What I have tried:

<pre>SELECT 
    Week, 
    TeamNum , 
    Gm1, 
    Gm2, 
    Gm3, 
    Gm1 + Gm2 + Gm3 AS total, 
    @rt := @rt + Gm1 + Gm2 + Gm3 AS rt 
FROM 
     b_Scores
        
join (SELECT @rt:=0 ) as dummy

这是我的代码，包括之前的答案。

This is my code with previous answer included.

(SELECT 'Week', 'TeamNum', 'Gm1', 'Gm2', 'Gm3','Total', 'rt' ,'Won', 'Lost', 'Ttl Wins', 'Ttl Losses')
UNION
( SELECT
 Week,
 TeamNum,
FORMAT( SUM(Gm1),0) AS "Gm1", 
FORMAT( SUM(Gm2),0) AS "Gm2", 
FORMAT( SUM(Gm3),0) AS "Gm3", 
Format(SUM(Gm1)+ SUM(Gm2)+ SUM(Gm3),0) as "Total",
@rt := @rt + Gm1 + Gm2 + Gm3 AS rt , 
  WON,  LOST, 
Format(SUM(won),0) as "TtlWins",
Format(SUM(lost),0) as "TtlLosses" 
FROM b_Scores R 
	JOIN (SELECT @rt:=0 ) as dummy
    JOIN b_Matches 	
    ON Week = MatchWk 	AND TeamNum = MatchTm
/* WHERE Week < 4 and TeamNum < 4 */
GROUP BY TeamNum, Week 
ORDER BY `TeamNum`,`Week`)

结果：

Result:

Week	TeamNum	Gm1	Gm2	Gm3	Total	rt	Won	Lost	Ttl Wins	Ttl Losses
1	1	283	263	323	869	418	2	1	4	2
2	1	446	384	456	1,286	5463	0	3	0	9
3	1	405	380	395	1,180	10562	3	0	9	0
1	2	798	768	703	2,269	1459	1	2	4	8
2	2	748	811	738	2,297	6946	1	2	4	8
3	2	683	787	803	2,273	11853	0	3	0	12
1	3	599	701	580	1,880	3516	2	1	8	4
2	3	550	551	491	1,592	8960	3	0	12	0
3	3	564	694	604	1,862	14066	3	0	12	0

在此结果中出现以下错误。

1. rt（运行总计）按周分组时不起作用和团队。

2.总胜负没有正确计算。添加此部分是因为当我有其他连接时很难将以前的代码合并到我的代码中。



希望这些额外的数据有助于清晰。

In this result had the following errors.
1. rt (running total) did not work when grouped by week and team.
2. Total wins or losses did not calculate properly. Added this portion because had hard time incorporating previous code into my code when i had additional joins.

Hope this additional data helps for clarity.

推荐答案

试试这个：

SELECT Week, TeamNum , SUM(Gm1), SUM(Gm2), SUM(Gm3), 
SUM(Gm1 + Gm2 + Gm3) AS total,
(@rt := @rt + SUM(Gm1 + Gm2 + Gm3)) AS rt FROM tablename, (SELECT @rt:=0) as dummy GROUP BY Week

我无法解决方案1工作，所以我做了一些调整。我希望这是准确的：

I couldn't get Solution 1 working so I made some tweaks. I hope this is accurate:

SELECT 
	Week, 
    TeamNum , 
    Gm1, 
    Gm2, 
    Gm3, 
    Gm1 + Gm2 + Gm3 AS total, 
    @rt := @rt + Gm1 + Gm2 + Gm3 AS rt 
FROM 
     [tablename]
        
join (SELECT @rt:=0 ) as dummy 

这篇关于如何获得“运行总和”？或者在SQL中运行团队总分？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！