我读到您不能在WHERE子句中使用ALIAS，但是我仍然没有一个好的替代解决方案来实现我在下面尝试做的事情。我该如何处理DISTANCE计算，以便可以在WHERE子句中使用它？
SELECT n.nid AS nid, location.name AS location_name, (6371.0 * ACOS(SIN((location.latitude * RADIANS(1))) * SIN((28.755925 * RADIANS(1))) + COS((location.latitude * RADIANS(1))) * COS((28.755925 * RADIANS(1))) * COS((location.longitude * RADIANS(1)) - (-81.346395 * RADIANS(1))))) AS distance
FROM
node n
LEFT JOIN location_instance ON n.vid = location_instance.vid
LEFT JOIN location ON location_instance.lid = location.lid
WHERE (( (n.status = '1') AND (n.type IN ('locations')) AND (distance <= 100) ))
ORDER BY distance
LIMIT 10
MySQL扩展了HAVING子句的使用，该子句可用于此目的。如果查询不是聚合查询，则HAVING仍会进行过滤-但它允许使用别名。
因此，您可以编写：
SELECT n.nid AS nid, l.name AS l, (6371.0 * ACOS(SIN((l.latitude * RADIANS(1))) * SIN((28.755925 * RADIANS(1))) + COS((l.latitude * RADIANS(1))) * COS((28.755925 * RADIANS(1))) * COS((l.longitude * RADIANS(1)) - (-81.346395 * RADIANS(1))))) AS distance
FROM node n LEFT JOIn
location_instance li
ON n.vid = li.vid LEFT JOIN
location l
ON li.lid = l.lid
WHERE n.status = 1 AND n.type IN ('locations')
HAVING distance <= 100
ORDER BY distance
LIMIT 10;
笔记：
status似乎是一个数字，则应将其与数字而不是字符串进行比较。status并type可以在进去HAVING条款，我怀疑他们是更好的WHERE（我怀疑HAVING可能会影响优化选项）。本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句