通过不工作来排序，而通过升序来获取距离

2hh7jdfx 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(357)

**结束。**此问题需要详细的调试信息。它目前不接受答案。
**想改进这个问题吗？**更新问题，使其成为堆栈溢出的主题。

两年前关门了。
改进这个问题

select * from `users` 
where exists (
    select *, (6371 * acos(cos(radians(22.559648)) * cos(radians(`lat`)) * cos(radians(`lng`) - radians(88.415552)) + sin(radians(22.559648)) * sin(radians(`lat`)))) AS distances 
    from `locations` 
    where `users`.`location_id` = `locations`.`id` 
    and (6371 * acos(cos(radians(22.559648)) * cos(radians(`lat`)) * cos(radians(`lng`) - radians(88.415552)) + sin(radians(22.559648)) * sin(radians(`lat`)))) < 32.688888 
    ORDER BY `distances` DESC)

mysql mysql-error-1064

来源：https://stackoverflow.com/questions/52211555/order-by-not-working-while-trying-to-get-distance-by-ascending-order

1条答案

按热度按时间

bwleehnv1#

你需要把 ORDER BY 在主查询中。子查询只是用来判断行是否存在，顺序无关紧要。
但是 distances 不是主查询中的列，因此不能对所编写的查询按它排序。而不是使用 EXISTS ，则需要与子查询联接。

SELECT u.*, (6371 * acos(cos(radians(22.559648)) * cos(radians(`lat`)) * cos(radians(`lng`) - radians(88.415552)) + sin(radians(22.559648)) * sin(radians(`lat`)))) AS distances 
FROM users AS u
JOIN locations AS l ON `u`.`location_id` = `l`.`id` 
HAVING distances < 32.688888 
ORDER BY distances DESC

赞(0）回复(0）举报 2021-06-20

我来回答

通过不工作来排序，而通过升序来获取距离

1条答案

相关问题

热门标签

最新问答