时间戳

首页 > 解决方案 > 如何获得子查询最近的距离？

mysql - 如何获得子查询最近的距离？

问题描述

我有一个查询来获取基于数据库表中存储的纬度和经度计算后的距离。

每个用户都有自己的纬度和经度，我试图获取离用户位置最近的中心名称。但是当有 2 个位置非常靠近用户时，我的查询会出错，我怎样才能获得可以在子查询中返回的最近的中心名称？

以下是我的 PHP 查询：

SELECT
t1.id, t2.name AS na_name, t3.name AS centre_name, t1.login, t1.login_lat, t1.login_long,
        (SELECT centre_name  
        from centre_location where
        (3956 * 2 * ASIN(SQRT( POWER(SIN(( t1.login_lat - centre_lat) *  pi()/180 / 2), 2) +COS( t1.login_lat * pi()/180) * COS(centre_lat * pi()/180) * POWER(SIN(( t1.login_long - centre_long) * pi()/180 / 2), 2) ))) <= 0.18
) AS LOGIN_LOCATION,  t1.login_location_accuracy,
        t1.logout,  t1.logout_lat, t1.logout_long, (SELECT centre_name  
        from centre_location where
        (3956 * 2 * ASIN(SQRT( POWER(SIN(( t1.logout_lat - centre_lat) *  pi()/180 / 2), 2) +COS( t1.logout_lat * pi()/180) * COS(centre_lat * pi()/180) * POWER(SIN(( t1.logout_long - centre_long) * pi()/180 / 2), 2) ))) <= 0.18 
) AS LOGOUT_LOCATION, t1.logout_location_accuracy,  t1.attendance_device_type , t1.remark
FROM attendance t1 
left join user t2 on t1.user_id = t2.id
left join centre t3 on t1.centre_id = t3.id
where t1.id = 10130

我确实尝试了 ORDER BY 和 LIMIT 1，但结果仍然不正确。请参考以下：

SELECT
t1.id, t2.name AS na_name, t3.name AS centre_name, t1.login, t1.login_lat, t1.login_long,
        (SELECT centre_name  
        from centre_location where
        (3956 * 2 * ASIN(SQRT( POWER(SIN(( t1.login_lat - centre_lat) *  pi()/180 / 2), 2) +COS( t1.login_lat * pi()/180) * COS(centre_lat * pi()/180) * POWER(SIN(( t1.login_long - centre_long) * pi()/180 / 2), 2) ))) <= 0.18
ORDER BY (3956 * 2 * ASIN(SQRT( POWER(SIN(( t1.login_lat - centre_lat) *  pi()/180 / 2), 2) +COS( t1.login_lat * pi()/180) * COS(centre_lat * pi()/180) * POWER(SIN(( t1.login_long - centre_long) * pi()/180 / 2), 2) ))) <= 0.18 DESC LIMIT 1
) AS LOGIN_LOCATION,  t1.login_location_accuracy,
        t1.logout,  t1.logout_lat, t1.logout_long, (SELECT centre_name  
        from centre_location where
        (3956 * 2 * ASIN(SQRT( POWER(SIN(( t1.logout_lat - centre_lat) *  pi()/180 / 2), 2) +COS( t1.logout_lat * pi()/180) * COS(centre_lat * pi()/180) * POWER(SIN(( t1.logout_long - centre_long) * pi()/180 / 2), 2) ))) <= 0.18 
ORDER BY (3956 * 2 * ASIN(SQRT( POWER(SIN(( t1.logout_lat - centre_lat) *  pi()/180 / 2), 2) +COS( t1.logout_lat * pi()/180) * COS(centre_lat * pi()/180) * POWER(SIN(( t1.logout_long - centre_long) * pi()/180 / 2), 2) ))) <= 0.18 DESC LIMIT 1
) AS LOGOUT_LOCATION, t1.logout_location_accuracy,  t1.attendance_device_type , t1.remark
FROM attendance t1 
left join user t2 on t1.user_id = t2.id
left join centre t3 on t1.centre_id = t3.id
where t1.id = 10130

center_name 应该只返回 1 个值，以便我可以显示/导出到我想要的结果，但有时 center_name 返回超过 1 行，我怎样才能得到最接近的返回值？

标签： mysql

解决方案

您的 ORDER BY 子句如下：

ORDER BY (3956 * 2 * ASIN(SQRT( POWER(SIN(( t1.login_lat - centre_lat) *  pi()/180 / 2), 2) +COS( t1.login_lat * pi()/180) * COS(centre_lat * pi()/180) * POWER(SIN(( t1.login_long - centre_long) * pi()/180 / 2), 2) ))) <= 0.18 DESC LIMIT 1

简而言之：

ORDER BY (<distance calculation>) <= 0.18 DESC LIMIT 1

虽然它应该是

ORDER BY (<distance calculation>) LIMIT 1

你应该删除<= 0.18. 您可能还想要ASC（最短距离优先）而不是DESC（最长距离优先）。

推荐阅读