MySQL距离和最小值与group by

Question

我有以下查询:

SELECT TeacherLocation.user_id, TeacherLocation.city, TeacherLocation.state_id, 
TeacherLocation.latitude, TeacherLocation.longitude,    

3959 * acos( cos( radians(40.76332092) ) * cos( radians( TeacherLocation.latitude ) )* 
cos( radians(   TeacherLocation.longitude ) - radians(-73.98623657) ) + sin(       
radians(40.76332092) ) * 
sin( radians( TeacherLocation.latitude ) )  ) AS distance 

FROM teacher_locations AS TeacherLocation

GROUP BY TeacherLocation.user_id
HAVING distance > 0
ORDER BY distance ASC LIMIT 0, 100

这似乎工作正常，但我遇到的问题是，由于我按 user_id 分组，并且 user_id 在表中不是唯一的，所以我可能并且确实获得了最近的距离，但不是始终是该距离的城市和 state_id(因为有多个)。结果:

user_id     city            stateId Lat             Long            Distance
83224   NEW YORK    33  40.751091   -73.99469757    0.954064642293979
87336   NEW YORK    33  40.751091   -73.99469757    0.954064642293979
87850   NEW YORK    33  40.751091   -73.99469757    0.954064642293979
86822   NEW YORK    33  40.751091   -73.99469757    0.954064642293979

如果我添加分钟(距离)，我会得到奇怪的结果。为什么？

Answer 1

您可以使用过滤连接，例如:

select  *
from    teacher_locations tl
join    (
        select  user_id
        ,       min(... distance formula here...) as MinDistance
        from    teacher_location
        group by
                user_id
        ) filter
on      filter.user_id = tl.user_id
        and filter.MinDisance = tl.(... distance formula here...)

如果速度是一个问题，您可以使用 MySQL 变量技巧。这更快，但不可移植(甚至可能不适用于 future 的 MySQL 版本。)

select  *
from    (
        select  @rn := if(user_id = @lastuserid, 1, @rn+1) as rn
        ,       @lastuserid := user_id
        ,       tl.*
        from    TeacherLocation tl
        cross join
               (select @rn := 0, @lastuserid := -1) as init
        order by
                tl.user_id
        ,       tl.distance desc
        ) as SubQueryAlias
where   rn = 1

Example at SQL Fiddle, you'll have to expand the distance calculation yourself.