如何修复嵌套的“运行总和”？表列使用2012年前的SQL

问题描述：

我需要在现有表格中添加RunningSum列。

表名= Rainbow_Lanes

列名是Bowler ，日期，Gm1（分数）和运行Ttl

使用下面的代码得到这些结果....

运行Ttl或Sum（Gm1 ）结果

I need to add a "RunningSum" column to an existing table.
The table name = Rainbow_Lanes

Column names are Bowler, Date, Gm1 (Score) and Running Ttl
Used code below with these results....

Running Ttl or Sum(Gm1) results

Gm1    Running Ttl
173      346           2x Gm1
150      600           ????
153      306           2x Gm1
147      294
173      519
224      224

I试图以不同的方式改变公式，但无法运行总和。

使用此链接的格式，直到我试图将其嵌入到我的代码中？

在SQL Server中计算简单运行总计 [ ^ ]

我的尝试：

I tried to change formula in different ways but could not get running sum to work.
Used format from this link which worked until i tried to nest it into my code?

Calculating simple running totals in SQL Server[^]

What I have tried:

SELECT  Bowler,  Date,  Gm1, 
     (      SELECT SUM(Gm1)	FROM Rainbow_Lanes 
                  	WHERE Gm1 = o.Gm1	
                  	AND Date  <= o.Date
        		ORDER BY Date, Bowler
     )  AS  "Running Ttl"

FROM Rainbow_Lanes 
WHERE Bowler = "Ray McDonough"
ORDER BY Date

..... ....................

.........................

答

试试

Try

SELECT Bowler, Date, Gm1,  
 ( SELECT SUM(Gm1) FROM Rainbow_Lanes L
 WHERE L.Bowler = R.Bowler 
 AND L.Date <= R.Date
 ) as [Running Total]

FROM Rainbow_Lanes R

where bowler = "Ray McDonough"

ORDER BY r.Date

可以排除最后一个where子句以带回所有保龄球。

The last where clause can be excluded to bring back all bowlers.

如何修复嵌套的“运行总和”？表列使用2012年前的SQL

相关推荐