表Car_Pos存放车辆行驶数据,结构如下:
字段名 字段类型 字段描述
Id int 递增字段
CarId int 车辆ID,一个ID表示一辆车
mileage int 车辆里程
CarPos Datetime 数据添加到数据时间,默认值: getdate() 车辆行驶记录是由多点够成,所以第N点的里程是=第N-1点+走过的距离.测试数据如下:1 4 30 '2009-07-24 16:14:36'
2 4 50 '2009-07-24 16:15:36'
3 4 70 '2009-07-24 16:16:36'
4 4 90 '2009-07-24 16:17:36'
5 4 100 '2009-07-24 16:18:36'
6 4 110 '2009-07-24 16:19:36'
7 4 150 '2009-07-24 16:20:36'
8 4 160 '2009-07-24 16:21:36'9 5 80 '2009-07-24 16:18:36'
10 5 110 '2009-07-24 16:19:36'
11 5 150 '2009-07-24 16:20:36'
12 6 160 '2009-07-24 16:21:36'上面是两辆车记录,需要输出形式为:4 130
5 80高手指教了.谢谢...
字段名 字段类型 字段描述
Id int 递增字段
CarId int 车辆ID,一个ID表示一辆车
mileage int 车辆里程
CarPos Datetime 数据添加到数据时间,默认值: getdate() 车辆行驶记录是由多点够成,所以第N点的里程是=第N-1点+走过的距离.测试数据如下:1 4 30 '2009-07-24 16:14:36'
2 4 50 '2009-07-24 16:15:36'
3 4 70 '2009-07-24 16:16:36'
4 4 90 '2009-07-24 16:17:36'
5 4 100 '2009-07-24 16:18:36'
6 4 110 '2009-07-24 16:19:36'
7 4 150 '2009-07-24 16:20:36'
8 4 160 '2009-07-24 16:21:36'9 5 80 '2009-07-24 16:18:36'
10 5 110 '2009-07-24 16:19:36'
11 5 150 '2009-07-24 16:20:36'
12 6 160 '2009-07-24 16:21:36'上面是两辆车记录,需要输出形式为:4 130
5 80高手指教了.谢谢...
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-24 16:50:59
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([Col1] int,[Col2] int,[Col3] int,[Col4] datetime)
Insert tb
Select 1,4,30,'2009-07-24 16:14:36' union all
Select 2,4,50,'2009-07-24 16:15:36' union all
Select 3,4,70,'2009-07-24 16:16:36' union all
Select 4,4,90,'2009-07-24 16:17:36' union all
Select 5,4,100,'2009-07-24 16:18:36' union all
Select 6,4,110,'2009-07-24 16:19:36' union all
Select 7,4,150,'2009-07-24 16:20:36' union all
Select 8,4,160,'2009-07-24 16:21:36' union all
Select 9,5,80,'2009-07-24 16:18:36' union all
Select 10,5,110,'2009-07-24 16:19:36' union all
Select 11,5,150,'2009-07-24 16:20:36' union all
Select 12,5,160,'2009-07-24 16:21:36'
Go
--Select * from tb-->SQL查询如下:
select a.col2,sum(b.col3-a.col3) as col3
from tb a
join tb b
on a.[Col2]=b.[Col2]
and a.col1=b.col1-1
group by a.col2
/*
col2 col3
----------- -----------
4 130
5 80(2 行受影响)
*/修改楼主一个数字.
select CarId,(select max(mileage) from tb a where a.CarId=tb.CarId)-(select min(mileage) from tb a where a.CarId=#a.CarId) from tb group by CarId
Create table [tb](Id int,CarId int,mileage int,CarPos datetime)
Insert tb
Select 1,4,30,'2009-07-24 16:14:36' union all
Select 2,4,50,'2009-07-24 16:15:36' union all
Select 3,4,70,'2009-07-24 16:16:36' union all
Select 4,4,90,'2009-07-24 16:17:36' union all
Select 5,4,100,'2009-07-24 16:18:36' union all
Select 6,4,110,'2009-07-24 16:19:36' union all
Select 7,4,150,'2009-07-24 16:20:36' union all
Select 8,4,160,'2009-07-24 16:21:36' union all
Select 9,5,80,'2009-07-24 16:18:36' union all
Select 10,5,110,'2009-07-24 16:19:36' union all
Select 11,5,150,'2009-07-24 16:20:36' union all
Select 12,5,160,'2009-07-24 16:21:36'select CarId,
(select max(mileage) from tb a where a.CarId=tb.CarId)
-
(select min(mileage) from tb a where a.CarId=tb.CarId)
from tb group by CarId
-----------------结果------------------
4 130
5 80
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-24 17:07:21
----------------------------------------------------------------
--> 测试数据:[Car_Pos]
if object_id('[Car_Pos]') is not null drop table [Car_Pos]
create table [Car_Pos]([id] int,[CarId] int,[mileage] int,[CarPos] datetime)
insert [Car_Pos]
select 1,4,30,'2009-07-24 16:14:36' union all
select 2,4,50,'2009-07-24 16:15:36' union all
select 3,4,70,'2009-07-24 16:16:36' union all
select 4,4,90,'2009-07-24 16:17:36' union all
select 5,4,100,'2009-07-24 16:18:36' union all
select 6,4,110,'2009-07-24 16:19:36' union all
select 7,4,150,'2009-07-24 16:20:36' union all
select 8,4,160,'2009-07-24 16:21:36' union all
select 9,5,80,'2009-07-24 16:18:36' union all
select 10,5,110,'2009-07-24 16:19:36' union all
select 11,5,150,'2009-07-24 16:20:36' union all
select 12,6,160,'2009-07-24 16:21:36'
--------------开始查询--------------------------
select
a.CarId,sum(b.mileage-a.mileage) as mileage
from
Car_Pos a, Car_Pos b
where
a.CarId=b.CarId
and
a.[id]=b.[id]-1
group by
a.CarId
----------------结果----------------------------
/*CarId mileage
----------- -----------
4 130
5 70(所影响的行数为 2 行)
*/
,'总里程'=((select top 1 mileage from tb where carid=a.carid order by id desc )
-(select top 1 mileage from tb where carid=a.carid))
from tb a group by carid
select col2,max(col3)-min(col3) col3
from tb
group by col2
/*
col2 col3
----------- -----------
4 130
5 80(2 行受影响)
*/其实这样就行了.
你的肯定错误楼主的意思是 同样的CardID 用最后时间-开始时间的 mileage