MySQL 在商店定位器-Google 地图应用程序的分组查询中选择错误的列值

Question

首先我必须声明我是一个极端的新手。现在刚刚使用 PHP 和 MySQL 大约 4 周。如果我没有正确地格式化这个问题或者没有使用正确的艺术术语，请提前接受我的歉意。

我正在构建一个商店定位器应用程序。为了进行测试，我有一个名为“位置”的表，其中包含 5 家不同连锁餐厅的名称、地址和纬度/经度数据，总共 1500 条（位置）记录。

我让应用程序作为标准商店定位器运行良好，用户输入他们的地址和以英里为单位的距离进行搜索。当删除 GROUP BY 语句时，下面的代码会正确返回这些结果。例如，当用户输入地址和距离进行搜索时，SELECT 语句会返回该距离内的所有餐馆。

我的应用要求仅返回并显示用户指定距离内每个连锁餐厅的最近位置。我添加了 GROUP BY 语句来完成此任务。返回正确的记录数以及正确的 loc_name 和与用户的距离。然而，所有其他字段永远都不正确。它们似乎是从 MIN 值之外的其他记录中随机选择的。例如，返回的第一条记录是距离 4.38 英里的 DAIRY QUEEN - 正确。但是，4.38 英里处的 DAIRY QUEEN 的地址、州、城市等不正确。

我已经广泛阅读了有关 GROUP BY 的问题以及使用 INNER JOIN 的要求也许可以解决我的问题？ stackoverflow 最近的一个问答非常具体地解决了这个问题，请参阅 MySQL在分组依据查询中选择错误的列值。到目前为止，我读过的所有解决方案都让我使用计算出的距离作为执行 JOIN 的关键，但我不明白这是怎么可能的。

问题 1：如何构造 SELECT 语句以获得所需的结果：位置表中仅针对每个连锁餐厅的完整数据字段行？

关于我的代码的注释，它不像看起来那么可怕，也不需要理解它来解决我的问题：

MIN() 中的三角公式计算用户地址之间的英里距离（转换为纬度/经度）和每个位置记录的纬度/经度。相信我，这工作正常。

ORDER BY 13 语句：表示按 SELECT 中列出的第 13 个字段进行排序，在本例中它是别名“distance”。我提到这一点是因为我注意到这种语法并不为人所知。

WHERE 语句后面的代码检查用户的地址（以纬度/经度为单位）是否位于具有纬度/经度角的框中，这些角是用户指定的要搜索位置的距离。这称为“边界框”。它用于优化搜索时间。人们可以简单地测试“距离”是否小于用户输入的距离，但这需要读取整个位置文件。生产版本将包含大约一百万条记录。位置表有一个索引：(loc_lat,loc_lon,loc_id)。我的理解是，在WHERE语句中使用Bounding Box会限制需要读取的索引的范围。问题2：我的实现方式是这样吗，会按照我描述的方式进行处理吗？问题1的解决方案会保留优化吗？

预先感谢大家的帮助。我真的才刚接触 mySQL 和 PHP 4 周，正如你所看到的，这超出了我的理解范围？

---

我的问题归结为这一点。应该如何修改此 SELECT 以仅返回 1 个位置表记录，其中每个 loc_name 都有相应的字段，即距用户输入地址的最小距离？

SELECT loc_id,loc_name,loc_address_1,loc_address_2,loc_city,
       loc_state,loc_postal_code,loc_phone,loc_fax,
       loc_lat,loc_lon,loc_geocoded_status,
       MIN( ((ACOS( SIN( $lat * PI( ) /180 ) * SIN( loc_lat * PI( ) /180 ) + 
               COS( $lat * PI( ) /180 ) * COS( loc_lat * PI( ) /180 ) *
               COS( ($long - loc_lon) * PI( ) /180 ) ) *180 / PI( )) *60 * 1.1515) )
       AS distance  
FROM locations WHERE (loc_lat between $lat1 and $lat2
                  AND loc_lon between $lon1 and $lon2)
                  AND loc_geocoded_status = 1
GROUP BY loc_name
ORDER BY 13

Answer 1

四个星期以来你已经取得了很大的进步。它有助于包含最少的 DDL 和 INSERT 语句，以鼓励更多的人做出响应。

我将 GROUP BY 语句添加到
完成这个。正确的数量
记录返回正确的
loc_name 和与用户的距离。
然而，所有其他字段都是
永远不会正确。它们看起来是随机的
从其他记录中选择的
超出 MIN 值。

是的，这对于 MySQL 来说是正常的。 MySQL 标准分组依据一文解释了这种行为。

不确定的结果集是
当一个或多个时返回
SELECT 中的非聚合列
子句未在 GROUP BY 中列出
条款。中列出的列
SELECT 子句但排除在外
GROUP BY 子句返回无意义
值，因为它们是列值
从所有人中不确定地选择
预聚合行。

您需要一个确定的结果集，而不是不确定的结果集。此语句应该为您提供一个两列结果集，其中每个位置名称包含一行。

SELECT loc_name, MIN( ((ACOS( . . . ) AS distance  
FROM locations
GROUP BY loc_name

您应该能够在位置名称和距离上使用该语句和 JOIN 表达式来获取您需要的其他列。

我将算术包装在名为“distance”的函数中，然后

SELECT L1.*, C.*
FROM locations L1
INNER JOIN (SELECT L2.loc_name, 
                   MIN(distance($lat, $lon, 
                                L2.loc_lat, L2.loc_lon)) AS distance
            FROM locations L2
            GROUP BY L2.loc_name) C
ON   L1.loc_name = C.loc_name
 AND C.distance  = distance($lat, $lon, 
                          L1.loc_lat, L1.loc_lon)

您需要添加边界框信息。当我试图确保 JOIN 正常工作时，我忽略了它。我在内部 SELECT 子句中有一个不必要的 ORDER BY，但那是一个前咖啡因子句，所以我删除了它。

您可能还需要 loc_name 上的索引，因为它在 GROUP BY 中使用。请参阅 MySQL 文档以了解 EXPLAIN 语法。