按表中的分组记录运行总计 [英] Running total by grouped records in table

问题描述

我有一张这样的表(Oracle，10)

I have a table like this (Oracle, 10)

Account     Bookdate     Amount
      1     20080101        100
      1     20080102        101
      2     20080102        200
      1     20080103       -200
...

我需要的是按 Account order by Account asc 和 Bookdate asc 分组的新表，其中包含一个运行总计字段，如下所示:

What I need is new table grouped by Account order by Account asc and Bookdate asc with a running total field, like this:

Account     Bookdate     Amount     Running_total
      1     20080101        100               100
      1     20080102        101               201
      1     20080103       -200                 1
      2     20080102        200               200
...

有简单的方法吗?

提前致谢.

推荐答案

你真的需要额外的桌子吗?

Do you really need the extra table?

您可以通过一个简单的查询获得所需的数据，如果您希望它看起来像一个表格，您显然可以将其创建为一个视图.

You can get that data you need with a simple query, which you can obviously create as a view if you want it to appear like a table.

这将为您提供您正在寻找的数据:

This will get you the data you are looking for:

select 
    account, bookdate, amount, 
    sum(amount) over (partition by account order by bookdate) running_total
from t
/

这将创建一个视图来向您显示数据，就像它是一个表格一样:

This will create a view to show you the data as if it were a table:

create or replace view t2
as
select 
    account, bookdate, amount, 
    sum(amount) over (partition by account order by bookdate) running_total 
from t
/

如果你真的需要这张桌子，你的意思是你需要它不断更新吗?还是只打一次?显然，如果它是一次性的，您可以使用上述查询创建表作为选择".

If you really need the table, do you mean that you need it constantly updated? or just a one off? Obviously if it's a one off you can just "create table as select" using the above query.

我使用的测试数据是:

create table t(account number, bookdate date, amount number);

insert into t(account, bookdate, amount) values (1, to_date('20080101', 'yyyymmdd'), 100);

insert into t(account, bookdate, amount) values (1, to_date('20080102', 'yyyymmdd'), 101);

insert into t(account, bookdate, amount) values (1, to_date('20080103', 'yyyymmdd'), -200);

insert into t(account, bookdate, amount) values (2, to_date('20080102', 'yyyymmdd'), 200);

commit;

忘记添加了；您指定要对表进行排序-这实际上没有意义，并且让我认为您的真正意思是您想要查询/视图-排序是您执行的查询的结果，而不是固有的东西表(忽略索引组织表等).

forgot to add; you specified that you wanted the table to be ordered - this doesn't really make sense, and makes me think that you really mean that you wanted the query/view - ordering is a result of the query you execute, not something that's inherant in the table (ignoring Index Organised Tables and the like).

这篇关于按表中的分组记录运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！