SQL Server 2008中每个组的运行总数

Question

我的表中有3列：Group，Date和Money。

Group Day Money Column4 (Does not exist yet) 
A  1  $100  $100 
B  2  $500  $500 
A  3  $-150 $-50 
B  3  $900  $1400 

我想要做的是创建第4列，它基本上是每个组的运行总数。 A组的运行总数为（100,100-150 = 50）。

这在SQL Server 2008中可能吗？我的代码获得前3列如下：

Select [Group], [Day], sum(money) as [Money] 
From Table_Name 
Group by [Group], [Day] 

Answer 1

要做到这一点的最好方法是累计和。也许是时候更新到更新版本的SQL Server了？

在SQL Server 2008中，你可以使用apply或相关子查询：

Select [Group], [Day], sum(money) as [Money], 
     (select sum(t2.money) 
     from table_name t2 
     where t.group = t2.group and t2.day <= t.day 
     ) as Column4 
From Table_Name t 
Group by [Group], [Day]; 

Answer 2

您可以使用窗口函数sum找到累积性和：

select 
    t.*, 
    sum(Money) over (partition by [group] order by [Day]) col4 
from your_table t; 

所以，你的查询变为：

select t.*, 
    sum([Money]) over (
     partition by [group] order by [Day] 
     ) col4 
from (
    select [Group], 
     [Day], 
     sum(money) as [Money] 
    from Table_Name 
    group by [Group], 
     [Day] 
    ) t;