问此mySQL查询有问题：(使用WHERE with AS子句)
EN

Stack Overflow用户

提问于 2011-05-19 07:45:18

回答 1查看 558关注 0票数 2

我的SQL查询运行良好，直到我尝试在第4行和第10行分别添加“WHERE distance <10”和“chunk-of-calculation AS distance”。你知道我怎么才能修复它吗？谢谢!

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

php

mysql

sql

回答 1

Stack Overflow用户

回答已采纳

发布于 2011-05-19 07:47:02

您需要将公式放在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

通过名称引用distance的惟一方法是包装语句，并将其放入新SELECT语句中的一个表中。例如：

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

票数 3

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/6052311

复制

相似问题

问此mySQL查询有问题：(使用WHERE with AS子句)
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问此mySQL查询有问题：(使用WHERE with AS子句)EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问此mySQL查询有问题：(使用WHERE with AS子句)
EN