日期:2014-05-17 浏览次数:21471 次
select tb.*,sum(流入- 流出)over(partition by 公司编码,银行编码 order by 时期 asc) from tb
------解决方案--------------------
with t as ( select '20110000' a , '1001' b, '10' c, 1000 d, 0 e from dual union all select '20121210' a , '1001' b, '10' c, 20 d, 30 e from dual union all select '20121211' a , '1001' b, '10' c, 30 d, 100 e from dual union all select '20110000' a , '1002' b, '12' c, 800 d, 0 e from dual union all select '20121218' a , '1002' b, '12' c, 100 d, 90 e from dual union all select '20121219' a , '1002' b, '12' c, 200 d, 100 e from dual ) select tt.a,tt.b,tt.c,decode(substr(tt.a,-4),'0000',tt.d,lag(tt.m) over(partition by tt.b,tt.c order by tt.a)),tt.d,tt.e,tt.m from ( select t.a,t.b,t.c,t.d,t.e,sum(t.d-t.e)over(partition by t.b,t.c order by t.a) m from t ) tt
------解决方案--------------------
实测数据:
CREATE TABLE T131 ( Period VARCHAR2(20), CompCode VARCHAR2(20), BankCode VARCHAR2(20), InFlow NUMBER(6), OutFlow NUMBER(6) ); INSERT INTO T131 VALUES('20110000', '1001', '10', 1000, 0); INSERT INTO T131 VALUES('20121210', '1001', '10', 20, 30); INSERT INTO T131 VALUES('20121211', '1001', '10', 30, 100); INSERT INTO T131 VALUES('20110000', '1002', '12', 800, 0); INSERT INTO T131 VALUES('20111218', '1002', '12', 100, 90); INSERT INTO T131 VALUES('20111219', '1002', '12', 200, 100);