mysql - SQL 显示 DISTINCT 行以获得最低值

Question

我正在使用 MySQL 5.5。我有一个 SQL 问题。

我有下表(oneToMany - 一个员工到多个位置):

+----------+   +-------------------+  +-----------+
| EMPLOYEE |   | EMPLOYEE_LOCATION |  | LOCATION  |
 ----------     -------------------    -----------
|ID        |   |EMP_ID             |  | ID        |
|          |   |LOC_ID             |  | LATITUDE  |
|          |   |                   |  | LONGITUDE |
+----------+   +-------------------+  +-----------+

数据:

+----+          +-------------+       +-------------------------------+
| ID |          |EMP_ID|LOC_ID|       |ID | LATITUDE    | LONGITUDE   |  
+----+          +-------------+       +-------------------------------+
| 1  |          |  1   |  1   |       | 1 | 28.80000000 |-25.76666700 |
| 2  |          |  1   |  2   |       | 2 | 27.35000000 |-25.76266700 |
| 3  |          |  1   |  3   |       | 3 | 27.95000000 |-25.66666700 |
+----+          |  2   |  4   |       | 4 | 26.85000000 |-25.76666700 |
                |  2   |  5   |       | 5 | 28.85000000 |-25.76666700 |
                |  4   |  6   |       | 6 | 28.85000000 |-25.86666701 |
                +-------------+       +-------------------------------+

我的SQL:

select e.ID,
( 6371 * acos( cos( radians(37) ) * cos( radians( o.LATITUDE) ) * cos( radians( o.LONGITUDE) - radians(-122) ) + sin( radians(37) ) * sin( radians( o.LATITUDE) ) ) ) AS distance
from
    www.employee as e
inner join
    www.employee_location as eo
        on e.id=eo.EMP_ID 
inner join
    www.location as o
        on eo.LOC_ID=o.ID        
order by distance

正在返回:

ID| DISTANCE
--+------------------
3 | 8622.766267835324
2 | 8630.684502219874
1 | 8633.923476508438
1 | 8697.058324230797
1 | 8728.471585032592
2 | 8760.772628068693

我正在尝试制定我的 SQL，以便它只为每个 EMPLOYEE 返回一行(3 行)。返回的每一行都是具有按 DISTANCE 排序的最低 DISTANCE 值的行。

例如，我想要以下内容:

ID| DISTANCE
--+------------------
3 | 8622.766267835324
2 | 8630.684502219874
1 | 8633.923476508438

感谢任何帮助，谢谢。

Answer 1

不需要子查询。只需在您的查询中进行分组，并根据距离进行分组 -

select e.ID,
min(( 6371 * acos( cos( radians(37) ) * cos( radians( o.LATITUDE) ) * cos( radians( o.LONGITUDE) - radians(-122) ) + sin( radians(37) ) * sin( radians( o.LATITUDE) ) ) )) AS distance
from
    www.employee as e
inner join
    www.employee_location as eo
        on e.id=eo.EMP_ID 
inner join
    www.location as o
        on eo.LOC_ID=o.ID   
group by e.ID     
order by distance