mysql - 按最近的位置分组

Question

我有以下表结构:

table `leads`:
`id` int
`postcode` varchar

table `dealers`:
`id` int
`name` varchar

table `dealer_locs`:
`id` int
`id_dealer` int (this is what links the loc to the dealer)
`postcode` varchar
`distance` int
`lat` varchar (latitude of postcode in this row)
`long` varchar (longitude of postcode in this row)

每个经销商可以有多个 dealer_locs(位置)。我正在尝试输出一个表格，其中显示离潜在客户最近的经销商。

我有一个我编写的函数 (get_lat_long())，它获取我想要的任何邮政编码的经纬度。

考虑以下代码:

$lead['postcode'] = 'M6A 3A1';
$lat_long = get_lat_long($lead['postcode']);

$lat = $lat_long['lat'];
$long = $lat_long['long'];

$sql = mysql_query("select d.id, d.name,
                    (6371 * 3.1415926 * SQRT(($lat - dl.lat) * ($lat - dl.lat) + COS($lat / 57.2957795) * COS(dl.lat / 57.2957795) * ($long - dl.long) * ($long - dl.long)) / 180) as distance, 
                    dl.postcode as dl_postcode
                    from `dealer_locs` as dl
                    left join `dealers` as d on d.id = dl.id_dealer
                    where d.type='Real'
                    order by distance asc
                    ") or die(mysql_error());

现在这段代码确实有效，但是它显示了重复项，因为每个经销商都有多个位置。

上述查询和代码的示例输出:

d.name        | distance
Joes Dealer   | 11
Kevins Dealer | 13
Mikes Dealer  | 21
Kevins Dealer | 43
Mikes Dealer  | 44
Joes Dealer   | 78

我想要的输出:

d.name        | distance
Joes Dealer   | 11
Kevins Dealer | 13
Mikes Dealer  | 21

我的问题是我不知道如何写这个分组依据(甚至不认为这需要分组依据或者这是否是正确的做法)。

Answer 1

应该这样做:

select 
    d.id, -- you can take id from here if you want
    d.name,
    min(6371 * 3.1415926 * SQRT(($lat - dl.lat) * ($lat - dl.lat) + COS($lat / 57.2957795) * COS(dl.lat / 57.2957795) * ($long - dl.long) * ($long - dl.long)) / 180) as distance, 
    dl.postcode as dl_postcode
from `dealer_locs` as dl
left join `dealers` as d on d.id = dl.id_dealer
where d.type='Real'
group by d.id
order by distance asc