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

I can't seem to find a suitable solution for the following (probably an age old) problem so hoping someone can shed some light. I need to return 1 distinct column along with other non distinct columns in mySQL.

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

I have the following table in mySQL:

我在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

I would like SQL statement to return the DISTINCT name along with the destination, rating based on country.

我希望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

As you can see, James and Declan have different ratings, but the same name, so they are returned only once.

正如你所看到的，詹姆斯和德克兰有不同的评级，但名称相同，所以他们只返回一次。

The following query returns all rows because the ratings are different. Is there anyway I can return the above result set?

以下查询返回所有行，因为评级不同。无论如何我可以返回上面的结果集吗？

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

4 个解决方案

#1

Using a subquery, you can get the highest id for each name, then select the rest of the rows based on that:

使用子查询，您可以获得每个名称的最高ID，然后根据以下内容选择其余行：

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

If you'd prefer, use MIN(id) to get the first record for each name instead of the last.

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

It can also be done with an INNER JOIN against the subquery. For this purpose the performance should be similar, and sometimes you need to join on two columns from the subquery.

它也可以通过针对子查询的INNER JOIN来完成。为此，性能应该类似，有时您需要从子查询中连接两列。

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

#2

The problem is that distinct works across the entire return set and not just the first field. Otherwise MySQL wouldn't know what record to return. So, you want to have some sort of group function on rating, whether MAX, MIN, GROUP_CONCAT, AVG, or several other functions.

问题是，整个回归集中的不同作用而不仅仅是第一个字段。否则MySQL不知道要返回什么记录。因此，您希望在评级上具有某种组功能，无论是MAX，MIN，GROUP_CONCAT，AVG还是其他几个功能。

Michael has already posted a good answer, so I'm not going to re-write the query.

迈克尔已经发布了一个很好的答案，所以我不打算重新编写查询。

#3

You can do a group by:

您可以通过以下方式进行分组：

select min(id) as id, name, destination, avg(rating) as rating, country from TABLE_NAME group by name, destination, country

#4

I agree with @rcdmk . Using a DEPENDENT subquery can kill performance, GROUP BY seems more suitable provided that you have already INDEXed the country field and only a few rows will reach the server. Rewriting the query giben by @rcdmk , I added the ORDER BY NULL clause to suppress the implicit ordering by GROUP BY, to make it a little faster:

我同意@rcdmk。使用DEPENDENT子查询可以消除性能，GROUP BY似乎更合适，前提是您已经INDEXed国家/地区字段，只有几行将到达服务器。通过@rcdmk重写查询giben，我添加了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

#1