我有一个这种格式的表(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累积总和(按组)
发布评论