在SQLite中按日期运行总订单 [英] Running Total-Order By Date in SQLite

问题描述

我有一张桌子，如下:

    CREATE TABLE [TBL_test] ([ID] int PRIMARY KEY,[RDate] CHAR(10),[Debtor] int,
    [Creditor] int);

    insert into [TBL_test] values(1,'2012/10/11',100,0);
    **insert into [TBL_test] values(2,'2012/11/02',20,0);**
    insert into [TBL_test] values(3,'2012/11/09',0,5);
    **insert into [TBL_test] values(4,'2012/11/02',0,10);**

    select *,(select sum(t2.Debtor - t2.Creditor) from TBL_test t2 where t2.rdate <=
    t1.rdate                
    order by rdate) as RT
    from TBL_test t1
    order by rdate

结果

ID  RDate       Debtor  Creditor    RT
1   2012/10/11  100     0           100
2   2012/11/02  20      0           110
4   2012/11/02  0       10          110
3   2012/11/09  0       5           105

请告知我该如何解决该问题

Please advice how can I do to fix the problem on

推荐答案

不是证明解决方案，但可能对您有用，而无需更改日期值

not a proof solution, but might work for you, without the need to change datevalues

select *,(select sum(t2.Debtor - t2.Creditor) from TBL_test t2 where (t2.rdate < t1.rdate) or (t2.rdate = t1.rdate and t2.ID <= t1.ID) ) as RT
from TBL_test t1
order by rdate,ID

这篇关于在SQLite中按日期运行总订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！