我正在尝试从查询中的另一个表运行子查询
我的疑问如下:
SELECT *, (6371000 * acos(cos(radians(select point_oi.lng
from point_oi
where point_oi.name like '%Main Square%')
)
* cos(radians(restaurants.lat)) * cos(radians(restaurants.lng)
- radians(select point_oi.lng
from point_oi
where point_oi.name like '%Main Square%'
))
+ sin(radians(select point_oi.lng
from point_oi
where point_oi.name like '%Main Square%'))
* sin(radians(restaurants.lat)))) AS distance
FROM restaurants
HAVING distance < 500;当我运行查询时,我得到一个错误,告诉我在select附近有一个错误。我想使用嵌套的select查询从另一个表中获取lat和lng,而不是对这些值进行硬编码。我怎么才能解决这个问题。
谢谢你的帮助
发布于 2019-11-28 00:42:04
如果suquery返回超过一个错误行,则不应使用子查询来检索point_poi lat,lnt。
尝试使用适当的连接(在这种情况下,使用可以使用交叉连接的point_poi和餐馆之间不存在的关系)
SELECT restaurants.*,
(6371000 * acos(cos(radians(point_oi.lng ))
* cos(radians(restaurants.lat)) * cos(radians(restaurants.lng)
- radians(point_oi.lng ))
+ sin(radians(point_oi.lng ))
* sin(radians(restaurants.lat)))) AS distance
FROM restaurants
CROSS JOIN point_oi
WHERE point_oi.name like '%Main Square%'
AND (6371000 * acos(cos(radians(point_oi.lng ))
* cos(radians(restaurants.lat)) * cos(radians(restaurants.lng)
- radians(point_oi.lng ))
+ sin(radians(point_oi.lng ))
* sin(radians(restaurants.lat)))) < 500;发布于 2019-11-28 00:35:55
请尝试此查询,并让我知道您的问题已解决。
SELECT t.*
,(6371000 * acos(cos(radians(SELECT point_oi.lng FROM point_oi WHERE point_oi.name LIKE '%Main Square%')) * cos(radians(restaurants.lat)) * cos(radians(restaurants.lng) - radians(SELECT point_oi.lng FROM point_oi WHERE point_oi.name LIKE '%Main Square%')) + sin(radians(SELECT point_oi.lng FROM point_oi WHERE point_oi.name LIKE '%Main Square%')) * sin(radians(restaurants.lat)))) AS distance
FROM restaurants As t
WHERE distance < 500;https://stackoverflow.com/questions/59074665
复制相似问题