在MySQL中选择不同的列以及其他一些列

Question

我似乎找不到合适的解决方案来解决以下（可能是古老的）问题，所以希望有人能够提供一些信息。我需要在MySQL中返回1个不同列以及其他非不同列。

我在 MySQL 中有以下表：

id      name       destination     rating     country
----------------------------------------------------
1       James      Barbados        5          WI
2       Andrew     Antigua         6          WI
3       James      Barbados        3          WI
4       Declan     Trinidad        2          WI
5       Steve      Barbados        4          WI
6       Declan     Trinidad        3          WI

我想要一个SQL语句，它可以返回根据国家排列的DISTINCT名称以及目的地和评分。

id      name       destination     rating     country
----------------------------------------------------
1       James      Barbados        5          WI
2       Andrew     Antigua         6          WI
4       Declan     Trinidad        2          WI
5       Steve      Barbados        4          WI

正如您所看到的，詹姆斯和德克兰拥有不同的评分，但是相同的姓名，因此它们只返回一次。

由于评分不同，以下查询会返回所有行。是否有办法返回上面的结果集？

SELECT (distinct name), destination, rating 
  FROM table 
 WHERE country = 'WI' 
 ORDER BY id

- user1038814

Answer 1

使用子查询，可以获取每个名称中最高的id，然后基于此选择其余行：

SELECT * FROM table
WHERE id IN (
  SELECT MAX(id) FROM table GROUP BY name
)

如果您愿意，可以使用MIN（id）来获取每个名称的第一条记录，而不是最后一条记录。

也可以通过对子查询使用INNER JOIN来完成。在这种情况下，性能应该是相似的，并且有时需要在子查询中连接两个列。

SELECT
  table.*
FROM 
  table
  INNER JOIN (
    SELECT MAX(id) AS id FROM table GROUP BY name
  ) maxid ON table.id = maxid.id

- Michael Berkowski

---

正如在这里 https://dev59.com/pGgu5IYBdhLWcg3wBym1#13617822 所示，如果您需要有多个“不同”的列，则需要多次分组：SELECT name, destination, MAX(id) AS id FROM table GROUP BY name, destination;（或包装在 SELECT * FROM table WHERE id IN (…) 中）。 - WoodrowShigeru

Answer 2

问题在于distinct作用于整个返回结果集而不仅仅是第一个字段。否则MySQL将不知道要返回哪条记录。所以，你需要对rating使用某种分组函数，无论是MAX、MIN、GROUP_CONCAT、AVG还是其他几个函数。

Michael已经发表了一个好答案，因此我不会重新编写查询。

- Brian Hoover

Answer 3

您可以使用GROUP BY子句：

SELECT MIN(id) AS id, name, destination, AVG(rating) AS rating, country
FROM TABLE_NAME
GROUP BY name, destination, country

与子查询的替代方案相比，这个查询在大型数据集中的表现更好，并且更容易阅读。

- Ricardo Souza

Answer 4

我同意@rcdmk的观点。使用DEPENDENT子查询可能会影响性能，如果你已经对< i >country字段进行了索引，并且只有少数行将到达服务器，则GROUP BY似乎更合适。重写由@rcdmk给出的查询，我添加了 ORDER BY NULL 子句以抑制GROUP BY的隐式排序，使其速度更快：

SELECT MIN(id) as id, name, destination as rating, country 
FROM table WHERE country = 'WI' 
GROUP BY name, destination ORDER BY NULL

- georgepsarakis