我有2张桌子：候选人和candidate_location。该candidate_location表根据其type字段从其他3个表中的任意一个获取地图坐标。其结果是别名lat和lng这就需要查询的一部分使用以下的IF语句，其中它的作品了距离。
我收到一个错误，它无法在距离计算部分中找到“ lat”或“ lng”列，我想是因为它们是别名。我如何重组它以便它们在计算中被识别？
这是查询：
SELECT candidate.CandID,
IF (candidate_location.type = 1, p.latitude, IF (candidate_location.type = 2, a.latitude, pr.latitude)) AS lat,
IF (candidate_location.type = 1, p.longitude, IF (candidate_location.type = 2, a.longitude, pr.longitude)) AS lng,
IF (candidate_location.type = 2, a.standard_deviation,null) as standard_deviation,
( 3959 * acos( cos( radians(51.41019) ) * cos( radians(lat ) )
* cos( radians(lng) - radians(0.07222)) + sin(radians(51.41019))
* sin( radians(lat)))) AS distance
FROM candidate_location
LEFT JOIN geo_postcodes AS p ON (candidate_location.type = 1 AND candidate_location.postal_id = p.id)
LEFT JOIN geo_area_averages AS a ON (candidate_location.type = 2 AND candidate_location.postal_id = a.id)
LEFT JOIN geo_probability AS pr ON (candidate_location.type = 3 AND candidate_location.postal_id = pr.id)
LEFT JOIN candidate ON candidate_location.candid=candidate.CandID
正如Ashwin所提到的，您不能直接将结果列名称用作计算中的字段。您将需要再次声明它……除非您另外使用MySql变量。它们像内联编程一样工作。设置变量值，然后使用该值，但是您仍然可以保留该值作为相关的列名。
SELECT
candidate.CandID,
@varLat := COALESCE( p.latitude, a.latitude, pr.latitude) AS lat,
@varLong := COALESCE( p.longitude, a.longitude, pr.longitude) AS lng,
COALESCE( a.standard_deviation, null) as standard_deviation,
( 3959 * acos( cos( radians(51.41019) ) * cos( radians(@varLat ) )
* cos( radians(@varLat) - radians(0.07222)) + sin(radians(51.41019))
* sin( radians(@varLong)))) AS distance
FROM
( select @varLat := 0.00, @varLong := 0.00) sqlvars,
candidate_location CanLoc
LEFT JOIN geo_postcodes AS p
ON (CanLoc.type = 1 AND CanLoc.postal_id = p.id)
LEFT JOIN geo_area_averages AS a
ON (CanLoc.type = 2 AND CanLoc.postal_id = a.id)
LEFT JOIN geo_probability AS pr
ON (CanLoc.type = 3 AND CanLoc.postal_id = pr.id)
LEFT JOIN candidate
ON CanLoc.candid=candidate.CandID
另外，我使用的是COALESCE（）而不是嵌套的IF（）块。返回第一个非空值，因此，由于您的联接基于类型1、2或3，因此只有该行才具有限定值。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句