在MySQL中选择不同的列以及其他一些列 [英] Select distinct column along with some other columns in MySQL

问题描述

我似乎无法为以下(可能是一个古老的)问题找到合适的解决方案，因此希望有人能提供一些帮助.我需要在MySQL中返回1个不同的列以及其他不同的列.

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中具有下表:

I have the following table in 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名称以及目的地，并基于国家/地区进行评级.

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

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

如您所见，James和Declan的评级不同，但名称相同，因此只返回一次.

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

推荐答案

使用子查询，您可以获得每个名称的最高id，然后根据该名称选择其余的行:

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

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

如果愿意，可以使用MIN(id)来获取每个名称的第一个记录，而不是姓氏.

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

也可以使用INNER JOIN针对子查询来完成.为此，性能应该相似，有时您需要在子查询的两个列上进行连接.

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.

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

这篇关于在MySQL中选择不同的列以及其他一些列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！