对于给定的列值仅获得一行

Question

我查询的基本结构是：

• 我有一个带有个人资料信息的profiles表
• 我有一个带有位置坐标的locations表
• 我有一个location_assignment表，该表仅包含（profile_id，location_id）Pairs

每个配置文件都分配给一个或多个位置，我要做的就是搜索配置文件，然后按距离顺序返回到位置坐标。我这样做的询问是（仅包括相关部分）如下：

SELECT *, 
      (3959*acos(cos(radians(30.292424))*cos(radians(lat))*cos(radians(lng)-  
       radians(-97.73856))+sin(radians(30.292424))*sin(radians(lat)))) AS distance,
      `profiles`.`name` as profilename, 
      `profiles`.`profile_id` as profile_id
 FROM (`profiles`)
 JOIN `location_assignment` 
          ON `profiles`.`profile_id` =`location_assignment`.`profile_id`
 JOIN `locations` 
          ON `location_assignment`.`location_id` = `locations`.`location_id`
HAVING `distance` < 50
ORDER BY `distance`
LIMIT 3"

（Select Line中的Grosstastic Thing将locations表中的LAT/LNG字段转换为距给定输入LAT/LNG的距离）

但是，我的查询使配置文件在结果中多次出现，一次分配给他的每个位置。我希望每个配置文件仅出现一次，并提供最短距离的位置信息。

我的膝盖 - jerk反应是使用group_by location_id，但我想确保获得最小距离输入坐标的位置。

Answer 1

go longhorns！

让我们首先在位置表中找到正确的行。

SELECT DISTINCT location_id
  FROM locations
 ORDER BY your_spherical_cosine_law_distance_formula
 LIMIT 1

可以为您提供唯一的位置ID。

现在，您想将其用作子查询以获取适当的配置文件行。您确实喜欢这样：

 SELECT whatever
   FROM (
        SELECT DISTINCT location_id
          FROM locations
         ORDER BY your_spherical_cosine_law_distance_formula
         LIMIT 1
        ) AS one
   JOIN location_assignment AS la ON one.location_id = la.location_id
   JOIN profiles AS p on p.profile_id =la.profile_id

应该为您提供适当的配置文件列表，而无需重复。

您没有问这个问题，但我希望您的位置没有太多。您使用的查询必然会在整个桌子上扫描，并为每一行进行很多数学操作。您的HAVING子句确实无济于事。为了使此更快，您需要将距离搜索与边界范围搜索相结合。这可能会有所帮助。http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

Answer 2

我认为您应该将MIN()函数添加到距离计算中，以获取到每个配置文件最接近位置的距离。另外，通过个人资料信息将GROUP BY添加到组中。

（我知道MySQL允许返回不在GROUP BY中的列，但这不是我要推荐的内容，因此我从您的SELECT中删除了*）。

SELECT MIN(3959*acos(cos(radians(30.292424))*cos(radians(lat))*cos(radians(lng)-  
       radians(-97.73856))+sin(radians(30.292424))*sin(radians(lat)))) AS distance,
      `profiles`.`name` as profilename, 
      `profiles`.`profile_id` as profile_id
 FROM (`profiles`)
 JOIN `location_assignment` 
          ON `profiles`.`profile_id` =`location_assignment`.`profile_id`
 JOIN `locations` 
          ON `location_assignment`.`location_id` = `locations`.`location_id`
GROUP BY `profiles`.`name`, `profiles`.`profile_id`
HAVING `distance` < 50
ORDER BY `distance`
LIMIT 3"