表概况：
.....交易时间...........当前余额.....
.....(1)xxxx-xx-xx ........40000.0.....
.....(2)xxxx-xx-xx.........30000.0.....
.....(3)xxxx-xx-xx.........20000.0.....
.....(4)xxxx-xx-xx.........10000.0.....
大概是这种情况，我想求相邻两行的 交易时间 的差，注意是相邻两行，然后用40000.0×时间(2)与时间(1)的差×日利率=利息。
请高手帮帮忙吧，小弟很急的，最好能有代码
------解决方案--------------------
- SQL code
--id为(1),(2)列行字段select datadiff(dd,a.交易时间,b.交易时间)from tableName a left join tableName b on a.id = b.id+1
------解决方案--------------------
- SQL code
declare @t table(id int,d datetime,v float)insert into @t select 1,'2008-01-05',4000insert into @t select 2,'2008-01-03',2000insert into @t select 3,'2008-01-02',1000declare @利率 floatset @利率=0.03select a.*,a.v*datediff(d,b.d,a.d)[email protected] from @t a left join @t b on a.id=b.id-1/*id d v ----------- ----------------------- ---------------------- ----------------------1 2008-01-05 00:00:00.000 4000 2402 2008-01-03 00:00:00.000 2000 603 2008-01-02 00:00:00.000 1000 NULL*/
------解决方案--------------------
- SQL code
create table tbf(id int,tim varchar(10),acc decimal(22,4))insert into tbf values(1,'2009/01/01',40000)insert into tbf values(2,'2009/02/01',30000)insert into tbf values(3,'2009/03/01',20000)insert into tbf values(4,'2009/04/01',10000)insert into tbf values(5,'2009/04/07',60000)select datediff(dd,a.tim,b.tim),a.*from tbf a left join tbf b on a.id = b.id+1drop table tbf/* id tim acc----------- ----------- ---------- ---------------------------------------NULL 1 2009/01/01 40000.0000-31 2 2009/02/01 30000.0000-28 3 2009/03/01 20000.0000-31 4 2009/04/01 10000.0000-6 5 2009/04/07 60000.0000(5 行受影响)*/
------解决方案--------------------
- SQL code
select datadiff(dd,a.交易时间,b.交易时间) from tableName a left join tableName b on a.id = b.id+1
------解决方案--------------------
------解决方案--------------------
select datadiff(dd,a.交易时间,b.交易时间) from tableName a left join tableName b on a.id = b.id+1
------解决方案--------------------
在插入下一条的时候,就把上一条记录的利率啥的就算了,增加个字段来记录.呵呵.不怕他利率变化,呵呵.
在每条上复杂些,但在查询上,大大简化,呵呵.
希望对你有帮助.
------解决方案--------------------
对的，这个只是别名
楼上的解法就可以了
SQL codecreate table tbf(id int,tim varchar(10),acc decimal(22,4))
insert into tbf values(1,'2009/01/01',40000)
insert into tbf values(2,'2009/02/01',30000)
insert into tbf values(3,'2009/03/01',20000)
insert into tbf values(4,'2009/04/01',10000)
insert into tbf values(5,'2009/04/07',60000)
select datediff(dd,a.tim,b.tim),a.*
from tbf a
left join tbf b on a.id = b.id+1
drop table tbf
/*
id tim acc
----------- ----------- ---------- ---------------------------------------
NULL 1 2009/01/01 40000.0000
-31 2 2009/02/01 30000.0000
-28 3 2009/03/01 20000.0000
-31 4 2009/04/01 10000.0000
-6 5 2009/04/07 60000.0000