日期:2014-05-17 浏览次数:20542 次
select top 15 *, (select [CVS_Data]
from [CarVehicleStatus_tbl] WHERE [C_ID] =Car_tbl.C_ID and [CVS_Type] =256) as TotalCumulativeOil
,cast( (select [CVS_Data] from [CarVehicleStatus_tbl] WHERE [C_ID] = Car_tbl.C_ID and [CVS_Type] =2) as decimal(8,2) ) as TotalMileage
,cast( (select [CVS_Data] from [CarVehicleStatus_tbl] WHERE [C_ID] = Car_tbl.C_ID and [CVS_Type] =4) as decimal(8,2) ) as Totaltraveltime
--, TotalMileage/Totaltraveltime*3600 as AverageSpeed
, (select max(EndDatetime) from CarTrackRecording_tbl where [C_ID] = Car_tbl.C_ID) as trackEndDatetime
,(select max(CG_UpdateTime) from Car_Gps_tbl where [C_ID] = Car_tbl.C_ID) as gpsEndDatetime
--, TotalCumulativeOil/TotalMileage*100 as AverageFuelConsumption
from Car_tbl
left join CarUserInofo on CarUserInofo.UserInfoID=Car_tbl.UserInfoID
left join CarUboxCalibrate_tbl on CarUboxCalibrate_tbl.C_ID = Car_tbl.C_ID
left join CarSoftVersion_tbl on CarSoftVersion_tbl.CSV_CID = Car_tbl.C_ID
left join User_tbl on User_tbl.UserID = Car_tbl.UserID
where
--(TotalMileage between 20 and 30 ) and
--(AverageSpeed between 20 and 30 ) and
Car_tbl.C_ID
not in (select top (( 1 - 1)* 15) Car_tbl.C_ID from Car_tbl
left join CarUserInofo on CarUserInofo.UserInfoID=Car_tbl.UserInfoID
left join CarUboxCalibrate_tbl on CarUboxCalibrate_tbl.C_ID = Car_tbl.C_ID
left join CarSoftVersion_tbl on CarSoftVersion_tbl.CSV_CID = Car_tbl.C_ID
left join User_tbl on User_tbl.UserID = Car_tbl.UserID order by C_CreateTime desc ) order by C_CreateTime desc
SELECT * ,
( CASE WHEN Totaltraveltime = 0 THEN 0
ELSE TotalMileage / Totaltraveltime * 3600
END ) AS A