php - MySQL 地理位置显示错误的距离

Question

我使用下面的 SQL 查询来获取每条记录的经度和纬度的距​​离，但不明白为什么所有记录的距离显示相同？

SELECT
 max(if(`field_name`='store-name', `field_value`, null )) AS `store-name`,
 max(if(`field_name`='store-description', `field_value`, null )) AS `store-description`,
 max(if(`field_name`='store-longitude', `field_value`, null )) AS `store-longitude`,
 max(if(`field_name`='store-latitude', `field_value`, null )) AS `store-latitude`,
 ((ACOS( SIN( -27.486204 * PI( ) /180 ) * SIN( 'store-latitude' * PI( ) /180 ) + COS( -27.486204 * PI( ) /180 ) * COS( 'store-latitude' * PI( ) /180 ) * COS( (152.994962 - 'store-longitude') * PI( ) /180 ) ) *180 / PI( )) *60 * 1.1515) AS `distance`
FROM `wp_cf7dbplugin_submits` 
WHERE `form_name` = 'Add Store'  
GROUP BY `submit_time` 
ORDER BY `submit_time` DESC
LIMIT 0,100

输出:

Answer 1

在列名称周围使用反引号 (`)，而不是单引号 (')...它们是列名称，而不是字符串

SELECT *,
       ((ACOS( SIN( -27.486204 * PI( ) /180 ) * SIN( `store-latitude` * PI( ) /180 ) + COS( -27.486204 * PI( ) /180 ) * COS( `store-latitude` * PI( ) /180 ) * COS( (152.994962 - `store-longitude`) * PI( ) /180 ) ) *180 / PI( )) *60 * 1.1515) AS `distance`
   FROM ( SELECT
             max(if(`field_name`='store-name', `field_value`, null )) AS `store-name`,
             max(if(`field_name`='store-description', `field_value`, null )) AS `store-description`,
             max(if(`field_name`='store-longitude', `field_value`, null )) AS `store-longitude`,
             max(if(`field_name`='store-latitude', `field_value`, null )) AS `store-latitude`
        FROM `wp_cf7dbplugin_submits` 
       WHERE `form_name` = 'Add Store'  
       GROUP BY `submit_time` 
       ORDER BY `submit_time` DESC
       ) A
LIMIT 0,100

否则，MySQL 将尝试将带引号的字符串转换为数字以进行乘法，并将其视为 0