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

Question

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

每个用户都有自己的纬度和经度，我试图获取最接近用户位置的中心名称。但是当有 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 行，我怎样才能获得最接近的返回值？

Answer 1

您的 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 (最长距离优先)。