Выберите отдельный столбец вместе с некоторыми другими столбцами в MySQL

Кажется, я не могу найти подходящее решение для следующей (вероятно, возрастной) проблемы, поэтому надеемся, что кто-то сможет пролить свет. Мне нужно вернуть 1 отдельный столбец вместе с другими нечеткими столбцами в 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

Я бы хотел, чтобы оператор 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

Ответы

Ответ 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

Ответ 2

Проблема заключается в том, что различные работы работают по всему набору возвратов, а не только по первому полю. В противном случае MySQL не узнает, какую запись вернуть. Таким образом, вы хотите иметь какую-то функцию группы по рейтингу, будь то MAX, MIN, GROUP_CONCAT, AVG или несколько других функций.

Майкл уже опубликовал хороший ответ, поэтому я не буду переписывать запрос.

Ответ 3

Вы можете сделать group by:

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

Ответ 4

Я согласен с @rcdmk. Использование подзапроса DEPENDENT может привести к гибели производительности. GROUP BY выглядит более подходящим, если вы уже указали поле country, и только несколько строк достигнут сервера. Перезаписывая запрос giben 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