我现在有这样一个表CarLocation
create table CarLocation(id int identity(1,1),Vehicle_Cd varchar(16),gpstime datetime,distance float)
id 是自增的,Vehicle_Cd是车牌,gpstime 是GPS的时间,distance 是里程
给出一点数据,
id Vehicle_Cd gpstime distance
1 粤BJ5980 2011-06-05 15:30:30 2200
2 粤BJ5980 2011-06-05 15:31:00 2201
3 粤BJ5980 2011-06-05 15:31:30 2202
4 粤BJ5980 2011-06-05 15:32:00 2203
5 粤BJ5980 2011-06-05 15:32:30 0
6 粤BJ5980 2011-06-05 15:33:00 1
7 粤BJ5980 2011-06-05 15:33:30 2
8 粤BJ5980 2011-06-05 15:34:00 3
现在就是要算出这台车所行使的里程,而里程是每2条记录的差相加而来。现在问题就是当下一个记录的里程比上一个小的时候,里程差要重新算,然后2段里程差的值再相加而得到行使里程。请问大虾们应该怎么做呢? 这个例子就是跑了6公里。
我现在的语句是这样的 select t.id,t.Vehicle_Cd,t.gpstime,t.distance,zlichecha=t.Distance-t1.Distance
into #tempzd from CarLocation as t1 left join #distable t on t.id=t1.id+1 where t.Vehicle_Cd=t1.Vehicle_Cdselect sum(zlichecha) from #tempzd
into #tempzd from CarLocation as t1 left join CarLocation t on t.id=t1.id+1 where t.Vehicle_Cd=t1.Vehicle_Cdselect sum(zlichecha) from #tempzd
上面那个忘记改表名了
case when a.distance>b.distance then a.distance-b.distance
else 0 end
) as 里程
from CarLocation a,CarLocation b
where a.Vehicle_Cd = b.Vehicle_Cd
and a.gpstime = (select top 1 gpstime from CarLocation where Vehicle_Cd = b.Vehicle_Cd and gpstime > b.gpstime order by gpstime ,id)
group by a.Vehicle_Cd
insert into CarLocation select 1,'粤BJ5980','2011-06-05 15:30:30',2200
insert into CarLocation select 2,'粤BJ5980','2011-06-05 15:31:00',2201
insert into CarLocation select 3,'粤BJ5980','2011-06-05 15:31:30',2202
insert into CarLocation select 4,'粤BJ5980','2011-06-05 15:32:00',2203
insert into CarLocation select 5,'粤BJ5980','2011-06-05 15:32:30',0
insert into CarLocation select 6,'粤BJ5980','2011-06-05 15:33:00',1
insert into CarLocation select 7,'粤BJ5980','2011-06-05 15:33:30',2
insert into CarLocation select 8,'粤BJ5980','2011-06-05 15:34:00',3
go
;with cte as(
select * from CarLocation a where not exists(select 1 from CarLocation where id=a.id-1 and distance<a.distance)
union all
select * from CarLocation a where not exists(select 1 from CarLocation where id=a.id+1 and distance>a.distance)
),cte1 as(
select a.Vehicle_Cd,b.distance-a.distance distance from cte a inner join cte b on a.Vehicle_Cd=b.Vehicle_Cd and a.id<b.id
where b.distance>a.distance and not exists(select 1 from cte where Vehicle_Cd=a.Vehicle_Cd and distance>b.distance and distance<a.distance)
)select Vehicle_Cd,sum(distance)distance from cte1 group by Vehicle_Cd
/*
Vehicle_Cd distance
---------------- ----------------------
粤BJ5980 6(1 行受影响)*/
go
drop table CarLocation
我放到测试环境中有问题