组功能不支持参考 [英] Reference not supported on group function
本文介绍了组功能不支持参考的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在使用此查询时遇到一些问题,看来我不能使用别名作为按组查询的参考
I have some problem using this query, it appears that i cannot use alias as reference on group by query
SELECT v.V_VEHICLEID, v.V_LICENSENO,
ROUND((MAX(IF(DAY(WP_DATETIME) = 1, WP_ODOMETER, NULL)) - MIN(IF(DAY(WP_DATETIME) = 1, WP_ODOMETER, NULL))) / 1000) AS TRIP1,
...,
ROUND((MAX(IF(DAY(WP_DATETIME) = 31, WP_ODOMETER, NULL)) - MIN(IF(DAY(WP_DATETIME) = 31, WP_ODOMETER, NULL))) / 1000) AS TRIP31,
ROUND(MAX(IF(DAY(WP_DATETIME) = 1, WP_FUELREAD, NULL)) - MIN(IF(DAY(WP_DATETIME) = 1, WP_FUELREAD, NULL)),2) AS FUEL1,
...,
ROUND(MAX(IF(DAY(WP_DATETIME) = 31, WP_FUELREAD, NULL)) - MIN(IF(DAY(WP_DATETIME) = 31, WP_FUELREAD, NULL)),2) AS FUEL31,
(SELECT TRIP1 / FUEL1) AS ECON1,
...,
(SELECT TRIP31 / FUEL31) AS ECON31
FROM VEHICLES v
JOIN WAYPOINTS wp on wp.WP_VEHICLEID = v.V_VEHICLEID
WHERE MONTH(wp.WP_DATETIME) = '6' AND v.V_USER = 'tc'
GROUP BY wp.WP_VEHICLEID
错误
不支持参考"TRIP1"(参考组功能)
Reference 'TRIP1' not supported (reference to group function)
任何解决方案?
推荐答案
您不能在同一查询中使用在查询中定义的列别名.尝试如下操作:
You can't use the column aliases you defined in a query in the same query. Try something like the following:
SELECT *,
TRIP1 / FUEL1 AS ECON1,
TRIP31 / FUEL31 AS ECON31,
OTHER COLUMNS....
FROM
(
SELECT v.V_VEHICLEID,
v.V_LICENSENO,
ROUND((MAX(IF(DAY(WP_DATETIME) = 1, WP_ODOMETER, NULL)) - MIN(IF(DAY(WP_DATETIME) = 1, WP_ODOMETER, NULL))) / 1000) AS TRIP1,
ROUND(MAX(IF(DAY(WP_DATETIME) = 1, WP_FUELREAD, NULL)) - MIN(IF(DAY(WP_DATETIME) = 1, WP_FUELREAD, NULL)),2) AS FUEL1,
ROUND((MAX(IF(DAY(WP_DATETIME) = 31, WP_ODOMETER, NULL)) - MIN(IF(DAY(WP_DATETIME) = 31, WP_ODOMETER, NULL))) / 1000) AS TRIP31,
ROUND(MAX(IF(DAY(WP_DATETIME) = 31, WP_FUELREAD, NULL)) - MIN(IF(DAY(WP_DATETIME) = 31, WP_FUELREAD, NULL)),2) AS FUEL31,
OTHER COLUMNS...
FROM VEHICLES v
JOIN WAYPOINTS wp
ON wp.WP_VEHICLEID = v.V_VEHICLEID
WHERE MONTH(wp.WP_DATETIME) = '6'
AND v.V_USER = 'tc'
GROUP BY wp.WP_VEHICLEID
) t
这篇关于组功能不支持参考的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文