[讨论][分享]求下一条记录与上一条记录的差值

wuaza

数据多的话，我这个明显要快。

select id,金额,金额 as 差额 from 表1 where id =1
union
SELECT a.id,a.金额,min (a.金额-b.金额 ) as 差额 from 表1 as a,表1 as b where b.id < a.id group by a.id,a.金额 ;

如果id按降序排列，只要把"<"改为">"就可以了。

[此贴子已经被作者于2005-12-30 18:40:20编辑过]

wuaza

select id,金额,金额 as 差额 from 表1 where id =1
union
SELECT a.id,a.金额,(a.金额-b.金额 ) as 差额 from 表1 as a,表1 as b where b.id = dmax("id","表1","id<" & a.id )

wuaza

我前面几个献丑了，大伙不要试，机器会算死的。呵呵～～

再来一个，速度稍好，但与情比金坚的没法比。

SELECT a.id, a.金额, a.金额-nz((select 金额 from 表1 where id=(select max(id) from 表1 where id<a.id)),0) AS 差额
FROM 表1 AS a;