select a.tr_drcr, b.amt2-a.amt1 from
(select sum(tr_amt) as amt1,tr_drcr from trfile where tr_drcr=2 group by tr_date,tr_drcr) a
(select sum(tr_amt) as amt2,tr_drcr from trfile where tr_drcr=2 group by
tr_date,tr_drcr) b
where a.tr_drcr=b.tr_drcr
(select sum(tr_amt) as amt1,tr_drcr from trfile where tr_drcr=2 group by tr_date,tr_drcr) a
(select sum(tr_amt) as amt2,tr_drcr from trfile where tr_drcr=2 group by
tr_date,tr_drcr) b
where a.tr_drcr=b.tr_drcr
insert into test values('2000/3/1',false,30);insert into test values('2000/3/2',false,45);
insert into test values('2000/3/2',true,60);insert into test values('2000/3/5',true,60);
insert into test values('2000/3/5',true,10);insert into test values('2000/3/5',false,60);
insert into test values('2000/3/5',false,10);
//select 语句
select a1.tr_date,(select sum(case when a2.tr_drcr=true then a2.tr_amt else -a2.tr_amt end) from test a2 where a2.tr_date<=a1.tr_date) as sub from test a1//结果:
tr_date | sub
------------+-------
2000-03-01 | 20.00
2000-03-01 | 20.00
2000-03-02 | 35.00
2000-03-02 | 35.00
2000-03-05 | 35.00
2000-03-05 | 35.00
2000-03-05 | 35.00
2000-03-05 | 35.00
tr_date | sub
------------+-------
2000-03-01 | 20.00
2000-03-02 | 35.00
2000-03-05 | 35.00