怎么用聚合列进行计算
sql 语句如下, 在结果集中有几个聚合列, 现在需要用这几个聚合列参与运算得出第三个列, 并且这些列需要参与where判断(如注释部分). 请问要怎么写
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 AverageSpeed ,
( CASE WHEN TotalMileage = 0 THEN 0
ELSE TotalCumulativeOil / TotalMileage * 100
END ) AS AverageFuelConsumption
FROM Car_tbl
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
LEFT JOIN ( SELECT MAX(EndDatetime) AS trackEndDatetime ,
C_ID
FROM CarTrackRecording_tbl
GROUP BY c_id
) t ON t.C_ID = Car_tbl.C_ID
LEFT JOIN ( SELECT MAX(CG_UpdateTime) AS gpsEndDatetime ,
C_ID
FROM Car_Gps_tbl
GROUP BY c_id
) t2 ON t2.C_ID = Car_tbl.C_ID
LEFT JOIN ( SELECT CAST([CVS_Data] AS DECIMAL(9, 2)) AS Totaltraveltime ,
C_ID ,
[CVS_Type]
FROM [CarVehicleStatus_tbl]
) t3 ON t3.C_ID = Car_tbl.C_ID
AND t3.[CVS_Type] = 4
LEFT JOIN ( SELECT CAST([CVS_Data] AS DECIMAL(9, 2)) AS TotalMileage ,
C_ID ,
[CVS_Type]
FROM [CarVehicleStatus_tbl]
) t4 ON t4.C_ID = Car_tbl.C_ID
AND t4.[CVS_Type] = 2
LEFT JOIN ( SELECT CAST([CVS_Data] AS DECIMAL(9, 2)) AS TotalCumulativeOil ,
C_ID ,
[CVS_Type]
FROM [CarVehicleStatus_tbl]
) t5 ON t5.C_ID = Car_tbl.C_ID
AND t5.[CVS_Type] = 256
WHERE --(TotalMileage between 20 and 30 ) and
( ( CASE WHEN Totaltraveltime = 0 THEN 0
ELSE TotalMileage / Totaltraveltime * 3600
END ) BETWEEN 200 AND 30000 )
