有表A (Createtime,MID,Inteset) 时间, 帐号 值 一个MID,不同时间,可能多条记录2013-07-24 01:10:20,10001,1012013-07-24 02:10:20,10001,902013-07-24 03:10:20,10001,1002013-07-24 03:10:20,10002,102......表B(MID,RemainMargin) 帐号,余额 一个MID,一个RemainMargin指 10001,10000 10002,20000首先按帐号,然后按时间排序:关联量表,能够直接得到以下记录(流水详细记录)时间 帐号 利息 结算后余额2013-07-24 01:10:20,10001,101, 10101 =(RemainMargin+Inteset)2013-07-24 02:10:20,10001, 90, 10191 =(上一个RemainMargin+Inteset)=10101+902013-07-24 03:10:20,10001,100,10291 =10191 +1002013-07-24 03:10:20,10002,102,20102 =20000+102......请大侠们帮看下,如何写一个语句到上表,不然我得用游标,一个个取速度很慢。非常感谢
这样的SQL语句如何写!!SQL
这样的SQL语句如何写!!SQL
insert into ta
select '2013-07-24 01:10:20',10001,101
union all select '2013-07-24 02:10:20',10001,90
union all select '2013-07-24 03:10:20',10001,100
union all select '2013-07-24 03:10:20',10002,102create table tb(帐号 varchar(10),余额 int)
insert into tb
select 10001,10000
union all select 10002,20000
select * from ta
select * from tbselect a.*,b.余额+(select sum(值) from ta t where t.时间<=a.时间 and t.帐号=a.帐号) as 结算后余额
from ta a
left join tb b on a.帐号=b.帐号drop table ta,tb/*
2013-07-24 01:10:20.000 10001 101 10101
2013-07-24 02:10:20.000 10001 90 10191
2013-07-24 03:10:20.000 10001 100 10291
2013-07-24 03:10:20.000 10002 102 20102*/
where a.帐号=b.帐号