这个mySQL查询有问题:(使用带有AS子句的WHERE）

My SQL query is working fine, until I try to add a 'WHERE distance < 10' and 'chunk-of-calculation AS distance' on 4th and 10th line respectively. Any idea how I can fix it? Thanks!

我的SQL查询工作正常,直到我尝试分别在第4行和第10行添加“WHERE距离<10”和“计算块距离”。知道如何解决它吗?谢谢!

Unknown column 'distance' in 'where clause'

SELECT SQL_CALC_FOUND_ROWS places.*, category.*, 
COUNT(places_reviews.place_id) AS num_reviews, 
(places_popularity.rating_1 + 2*places_popularity.rating_2 + 3*places_popularity.rating_3 + 4*places_popularity.rating_4 + 5*places_popularity.rating_5)/(places_popularity.rating_1 + places_popularity.rating_2 + places_popularity.rating_3 + places_popularity.rating_4 + places_popularity.rating_5) AS average_rating, 
6371 * acos( cos( radians(places.lat) ) * cos( radians( 1.29315 ) ) * cos( radians( 103.827164 ) - radians(places.lng) ) + sin( radians(places.lat) ) * sin( radians( 1.29315 ) ) ) AS distance 
FROM (places) 
JOIN category 
ON places.category_id = category.category_id 
LEFT JOIN places_reviews ON places_reviews.place_id = places.id 
LEFT JOIN places_popularity ON places_popularity.place_id = places.id 
WHERE `places`.`category_id` = 1 AND `distance` < 5 AND places.name LIKE '%%' GROUP 
BY places.id 
ORDER BY id desc 
LIMIT 5

1 个解决方案

#1

You will need to put the formula in your WHERE clause instead of using the alias distance. In a SQL query, the WHERE clause is evaluated before the SELECT statement so the alias (in this case distance) does not exist yet. Here is what your SQL statement will look like:

您需要将公式放在WHERE子句中,而不是使用别名距离。在SQL查询中,WHERE子句在SELECT语句之前计算,因此别名(在本例中为distance)尚不存在。以下是您的SQL语句的外观:

SELECT SQL_CALC_FOUND_ROWS places.*, category.*, 
COUNT(places_reviews.place_id) AS num_reviews, 
(places_popularity.rating_1 + 2*places_popularity.rating_2 + 3*places_popularity.rating_3 + 4*places_popularity.rating_4 + 5*places_popularity.rating_5)/(places_popularity.rating_1 + places_popularity.rating_2 + places_popularity.rating_3 + places_popularity.rating_4 + places_popularity.rating_5) AS average_rating, 
6371 * acos( cos( radians(places.lat) ) * cos( radians( 1.29315 ) ) * cos( radians( 103.827164 ) - radians(places.lng) ) + sin( radians(places.lat) ) * sin( radians( 1.29315 ) ) ) AS distance 
FROM (places) 
JOIN category 
ON places.category_id = category.category_id 
LEFT JOIN places_reviews ON places_reviews.place_id = places.id 
LEFT JOIN places_popularity ON places_popularity.place_id = places.id 
WHERE `places`.`category_id` = 1 
   AND (6371 * acos( cos( radians(places.lat) ) * cos( radians( 1.29315 ) ) * cos( radians( 103.827164 ) - radians(places.lng) ) + sin( radians(places.lat) ) * sin( radians( 1.29315 ) ) )) < 5
   AND places.name LIKE '%%' GROUP 
BY places.id 
ORDER BY id desc 
LIMIT 5

The only way you could refer to distance by name would be to wrap your statement and make it into a table in a new SELECT statement. For example:

您可以通过名称引用距离的唯一方法是包装您的语句并将其放入新SELECT语句中的表中。例如:

SELECT *
FROM ( <insert your original query here without the WHERE distance= statement ) AS t
WHERE distance < 5