SQL Server累积总和(按组) [英] SQL Server Cumulative Sum by Group

问题描述

我有一个这种格式的表(SQL Server 2005):

I have a table (SQL Server 2005) of this format:

虚拟身份证，注册日期，商品ID，数量，价格

，我想添加一个新列( cumulative )，该列按date_registered计算每个item_id订单的累计总数，如下所示:

and I want to add a new column (cumulative) which calculates the cumulative totals of each item_id order by date_registered as shown:

dummy_id  date_registered  item_id  quantity    price   cumulative

1           2013-07-01        100      10        34.5       10

2           2013-07-01        145       8        2.3         8

3           2013-07-11        100      20        34.5       30

4           2013-07-23        100      15        34.5       45

5           2013-07-24        145      10        34.5       18

先感谢

推荐答案

在SQL Server 2005中，我将使用相关子查询来做到这一点:

In SQL Server 2005, I would do this using a correlated subquery:

select dummy_id, date_registered, item_id, quantity, price,
       (select sum(quantity)
        from t t2
        where t2.item_id = t.item_id and
              t2.date_registered <= t.date_registered
       ) as cumulative
from table t;

如果您确实要将其添加到表中，则需要更改表以添加列，然后进行更新.如果表具有插入和更新，则需要添加触发器以使其保持最新状态.通过查询获取它绝对容易.

If you actually want to add this into a table, you need to alter the table to add the column and then do an update. If the table has inserts and updates, you will need to add a trigger to keep it up-to-date. Getting it through a query is definitely easier.

在SQL Server 2012中，您可以使用以下语法执行此操作:

In SQL Server 2012, you can do this using the syntax:

select dummy_id, date_registered, item_id, quantity, price,
       sum(quantity) over (partition by item_id order by date_registered) as cumulative
from table t;

这篇关于SQL Server累积总和(按组)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！