学步园

游标的使用因为要修改数据，前一条数据的一个字段减去后一条数据的一个字段，得出来的值再插入到前一条记录中，试过很多方法还是用游标比较方便。

sql语名如下　

deallocate test
declare @id bigint
declare @x float
declare @y float
declare @h float
declare test cursor
dynamic
for
select id=[GPSIndex],x=[X],y=[Y],h=[Height] from dbo.M8_clean where [Style]=2 order by GPSIndex desc
for read only
go
open test
declare @id bigint
declare @x float
declare @y float
declare @h float
declare @id1 bigint
declare @x1 float
declare @y1 float
declare @h1 float
declare @i int

set @i=0
fetch first from test into @id,@x,@y,@h
set @id1=@id
set @x1=@x
set @y1=@y
set @h1=@h
fetch next from test into @id,@x,@y,@h
while @@fetch_status=0
Begin
update dbo.M8_clean set XSpeed=(@x1-@x),YSpeed=(@y1-@y),Hspeed=(@h1-@h) where GPSIndex=@id1;
set @id1=@id
set @x1=@x
set @y1=@y
set @h1=@h
fetch next from test into @id,@x,@y,@h
end
go

deallocate test
declare @id bigint
declare @x float
declare @y float
declare @h float
declare test cursor
dynamic
for
select id=[GPSIndex],x=[X],y=[Y],h=[Height] from dbo.M4_clean where [Style]=3 order by GPSIndex desc
for read only
go
open test
declare @id bigint
declare @x float
declare @y float
declare @h float
declare @id1 bigint
declare @x1 float
declare @y1 float
declare @h1 float
declare @i int


set @i=0
fetch first from test into @id,@x,@y,@h
set @id1=@id
set @x1=@x
set @y1=@y
set @h1=@h
fetch next from test into @id,@x,@y,@h
while @@fetch_status=0
Begin
update dbo.M4_clean set XSpeed=(@x1-@x)/24,YSpeed=(@y1-@y)/24,Hspeed=(@h1-@h)/24 where GPSIndex=@id1;
set @id1=@id
set @x1=@x
set @y1=@y
set @h1=@h
fetch next from test into @id,@x,@y,@h
end
begin
update dbo.M4_clean set XSpeed=0,YSpeed=0,Hspeed=0 where GPSIndex=@id
end
go