选择不同的列以及 MySQL 中的其他一些列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8552192/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select distinct column along with some other columns in MySQL
提问by user1038814
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.
如您所见，James 和 Declan 的评分不同，但名称相同，因此它们只返回一次。
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
采纳答案by Michael Berkowski
Using a subquery, you can get the highest idfor 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 JOINagainst the subquery. For this purpose the performance should be similar, and sometimes you need to join on twocolumns 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
回答by Brian Hoover
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.
Michael 已经发布了一个很好的答案，所以我不打算重新编写查询。
回答by Ricardo Souza
You can do a group by:
你可以做一个group by：
select min(id) as id, name, destination, avg(rating) as rating, country from TABLE_NAME group by name, destination, country
回答by georgepsarakis
I agree with @rcdmk . Using a DEPENDENT subquery can kill performance, GROUP BY seems more suitable provided that you have already INDEXed the countryfield and only a few rows will reach the server. Rewriting the query giben by @rcdmk , I added the ORDER BY NULLclause to suppress the implicit ordering by GROUP BY, to make it a little faster:
我同意 @rcdmk 。使用 DEPENDENT 子查询会降低性能，如果您已经对国家/地区字段进行了索引并且只有几行会到达服务器，则 GROUP BY 似乎更合适。通过@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
