mysql - 如何在 MySQL 中为一列应用 DISTINCT 选择器

Question

我有如下表格:-

Table: deals+----+-------+----------+----------+------------+| ID |  name | latitude | longitude|  end_date  ||----+-------+----------+----------+------------+|  1 | pizza |    10.5  |   -10.5  | 2012-12-12 ||  2 | pizza |    11.5  |   -10.5  | 2012-12-12 ||  3 | jelly |    21.5  |   -10.5  | 2012-12-12 ||  4 | jelly |    23.5  |   -10.5  | 2012-12-12 ||  5 | lily  |    19.5  |   -10.5  | 2012-12-12 |+----+-------+----------+----------+------------+

I'm using the latitude and longitude to find the distance to that person. But I need result to be DISTINCT based on name. I also need to sort the results by the distance I calculated and apply the limit 0,3.

The query I'm currently using is"-

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;  

所以简而言之，我需要的是:-

1. 最近的交易列表(按距离排序)
2. 对 name 应用 distinct(这样披萨和果冻都只会出现一次)

Answer 1

您需要应用 MIN 来获取每个名称的最小距离，GROUP BY 名称(每个名称给出一个结果)和 ORDER BY distance first and id later;

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

我应该补充一点，这个查询可能适合您的情况，但通常不是很有用，因为如果您想知道例如最近的比萨饼店的纬度和经度，您将需要一个完全不同的查询。