select value-
isnull((select top 1 value from temp where id<pre.id order by id desc),0)
from tmp pre
where id!=第一行的id
isnull((select top 1 value from temp where id<pre.id order by id desc),0)
from tmp pre
where id!=第一行的id
isnull((select top 1 value from temp where id<pre.id order by id desc),0)
from tmp pre
where id!=第一行的id
from tmp a left join tmp b on a.id+1=b.id
from tmp a join tmp b on a.id+1=b.id
create table tmp(id int identity(1,1),value decimal(20,2))
insert into tmp
select 10
union all select 39
union all select 49
union all select 35
union all select 89
go
select value_diff=isnull(b.[value],0)-isnull(a.[value],0)
from tmp a join tmp b on a.id+1=b.id
go
drop table tmp/*--测试结果
value_diff
-----------------------
29.00
10.00
-14.00
54.00
--*/
declare @value1 numeric(20,3) --前行的值
declare @value2 numeric(20,3) --后行的值
declare @pos int --记录游标位置
declare @count int --记录数set @pos=1
--取得记录数
select @count=count(*) from test1
select @count
--定义游标
declare t_cur1 cursor scroll for
select value from 你的表
open t_cur1
--fetch next from t_cur1 into @value1
--select @value1
--定义游标
declare t_cur2 cursor scroll for
select value from 你的表
open t_cur2fetch next from t_cur2 into @value2
while @@FETCH_STATUS = 0
begin
fetch ABSOLUTE @pos from t_cur1 into @value1 --取前行值
set @pos=@pos+1
fetch ABSOLUTE @pos from t_cur2 into @value2 --取后行值
--select @value1,@value2
if @pos<=@count
begin
insert into @tbl values(@value2-@value1) --插入值
end
end
--select (@value2-@value1) as vv,@value1,@value2
select * from @tbl
CLOSE t_cur1
DEALLOCATE t_cur1
CLOSE t_cur2
DEALLOCATE t_cur2测试数据:
create table test1
(
id varchar(23),
value int
)
insert into test1 values('001',1)
insert into test1 values('002',2)
insert into test1 values('003',4)
insert into test1 values('004',7)
结果:
value
----------------------
1.000
2.000
3.000(所影响的行数为 3 行)
vileboy(丁仔)的方法肯定没有问题,但效率不高
问题依旧在期待中……