我有一个如下表:-
表:交易+----+-------+----------+----------+------------+|ID |名称|纬度|经度|结束日期||----+-------+----------+----------+------------+|1|披萨|10.5|-10.5|2012-12-12||2|披萨|11.5|-10.5|2012-12-12||3|果冻|21.5|-10.5|2012-12-12||4|果冻|23.5|-10.5|2012-12-12||5|lily |19.5|-10.5|2012-12-12|+----+-------+----------+----------+------------+
我用latitude
和longitude
来计算到那个人的距离。但我需要在name
的基础上得到DISTINCT
的结果。我还需要根据我计算的距离对结果进行排序,并应用极限0.3。
我目前使用的查询是"-
SELECT *,
( 6371 * Acos(Cos(Radians(9.939625)) * Cos(Radians(lat)) * Cos(
Radians(lng) - Radians(76.259498)) +
Sin(Radians(9.939625)) * Sin(Radians(lat))) ) AS
distance
FROM deals
WHERE 1
AND end_date >= Now()
HAVING distance < 20000
ORDER BY id DESC,
distance
LIMIT 0, 3;
简而言之,我需要的是:-
- 最接近的交易列表(按距离排序)
- 涂抹在
name
上(这样披萨和果冻只会出现一次)
您需要先应用MIN
来获得每个名称的最小距离、GROUP BY
名称(每个名称给出一个结果)和ORDER BY
距离,然后再应用id;
SELECT *,
MIN( 6371 * Acos(Cos(Radians(9.939625)) * Cos(Radians(latitude)) * Cos(
Radians(longitude) - Radians(76.259498)) +
Sin(Radians(9.939625)) * Sin(Radians(latitude))) )
AS distance
FROM deals
WHERE end_date >= Now()
GROUP BY name
ORDER BY distance, id DESC
我应该补充一点,这个查询可能适合你的情况,但通常不是很有用,因为如果你想知道最近的披萨店的纬度和经度,你需要一个完全不同的查询。
SELECT *,
( 6371 * Acos(Cos(Radians(9.939625)) * Cos(Radians(lat)) * Cos(
Radians(lng) - Radians(76.259498)) +
Sin(Radians(9.939625)) * Sin(Radians(lat))) ) AS
distance
FROM deals
WHERE 1
AND end_date >= Now()
GROUP BY name
HAVING distance < 20000
ORDER BY id DESC,
distance