如何用聚合列进行计算

怎么用聚合列进行计算
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

[解决办法]
T-SQL查询的顺序是先from再where再select的，而你where中出现的AverageSpeed是在select中才生成的，所以不行，可以试下：

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 )

[解决办法]
这个好像不行，毕竟T-SQL就是这样规定的。
[解决办法]

引用:

这样是可以了,但是 where部分就有点复杂了, 我想达到前端的筛选到后端是透明的
引用:
T-SQL查询的顺序是先from再where再select的，而你where中出现的AverageSpeed是在select中才生成的，所以不行，可以试下：
SQL code?12345678910111213141516171819202122232……

你再子查询一次就行了?
SELECT A FROM (SELECT 1 AS A ) WHERE A=1
[解决办法]
效率肯定受影响的了，建议用存储过程实现了

如何用聚合列进行计算

热点推荐