mysql - 根据每个作业的最短距离获取单个对应行

Question

我正在构建一个系统，用户可以在其中使用邮政编码进行搜索(很快会一次搜索多个邮政编码)，并且每个“工作”可以有最多三个不同的位置。我需要返回最短距离 任务的 ID，以及计算出的距离和该任务的所有其他字段。

我可以计算作业的距离。我什至可以为每个作业返回一个结果。

但我无法返回每个作业的最小距离结果。

我在这里整理了一个 SQL fiddle :http://sqlfiddle.com/#!2/03b2c/10

示例数据:

输入:SET @user_lat = 44.053575, @user_lng = -123.086493, @range = 50;

ID  JOB_ID  LATITUDE    LONGITUDE               DISTANCE

9   1       44.063716888428 -123.08470916748    0
8   1       44.052070617676 -123.086753845215   0.81
7   1       44.035049438477 -123.047355651855   2.71

10  2       44.059913635254 -123.017936706543   3.32
11  2       44.045707702637 -122.930877685547   7.73
13  3       45.480751037598 -122.642738342285   100.21

GROUP 结果: - 每行都可以，但不是最近的距离。

ID  JOB_ID  LATITUDE        LONGITUDE           DISTANCE
7   1       44.035049438477 -123.047355651855   2.71
10  2       44.059913635254 -123.017936706543   7.73

ROUND( [math], 2 ) AS distance -- (full query below)
...
GROUP BY job_id

HAVING distance < @range

ORDER BY distance ASC;

GROUP 和 Min() 结果: - 正确返回了最小距离，但返回的 ID 不正确。

ID  JOB_ID  LATITUDE        LONGITUDE           DISTANCE
7   1       44.035049438477 -123.047355651855   0
10  2       44.059913635254 -123.017936706543   3.32

MIN( ROUND( [math], 2 ) ) AS distance
...
GROUP BY job_id

HAVING distance < @range

ORDER BY distance ASC;

__

我需要什么: - 返回最小距离，并且 ID 与该距离匹配。

ID  JOB_ID  LATITUDE        LONGITUDE           DISTANCE
9   1       44.035049438477 -123.047355651855   0
11  2       44.059913635254 -123.017936706543   3.32

__

来自组结果示例的完整查询

set
  @user_lat = 44.063717, 
  @user_lng = -123.084706,
  @range = 2000;


-- Filter by distance, displaying the nearest location of each job in miles
select 
  *,

  -- We can use min() here to get the lowest distance, but this does not affect the ID returned!
  -- min( ... ) as distance

  ROUND(3956 * 2 * ASIN(SQRT(POWER(SIN( (@user_lat - abs(loc.latitude))  * pi()/180 / 2),2) + COS(@user_lat * pi()/180 ) * COS( abs(loc.latitude) * pi()/180) * POWER(SIN((@user_lng - loc.longitude) * pi()/180 / 2), 2) )), 2)
    as distance

from `locations` loc

group by job_id

having distance < @range

order by distance asc;

Answer 1

因为是mysql，你可以利用它特殊的分组实现，做一个非常简单的查询:

select * from (
  select *,
  ROUND(3956 * 2 * ASIN(SQRT(POWER(SIN( (@user_lat - abs(loc.latitude))  * pi()/180 / 2),2) + COS(@user_lat * pi()/180 ) * COS( abs(loc.latitude) * pi()/180) * POWER(SIN((@user_lng - loc.longitude) * pi()/180 / 2), 2) )), 2) as distance
  from locations
  order by distance) loc
group by job_id