Select distinct column zusammen mit einigen anderen Spalten in MySQL

Question

Ich kann nicht scheinen, um eine geeignete Lösung für das folgende (wahrscheinlich eine uralte) Problem zu finden, so hoffen, dass jemand etwas Licht zu vergießen kann. Ich muss 1 eindeutige Spalte zusammen mit anderen nicht eindeutigen Spalten in mySQL zurückgeben.

Ich habe die folgende Tabelle 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

Ich möchte, dass die SQL-Anweisung den DISTINCT-Namen zusammen mit dem Ziel zurückgibt, Bewertung auf der Grundlage von Land.

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

Wie Sie sehen können, haben James und Declan unterschiedliche Bewertungen, aber denselben Namen, so dass sie nur einmal zurückgegeben werden.

Die folgende Abfrage gibt alle Zeilen zurück, da die Bewertungen unterschiedlich sind. Gibt es eine Möglichkeit, die obige Ergebnismenge zurückzugeben?

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

Answer 1

Mit einer Unterabfrage können Sie die höchste id für jeden Namen, und wählen Sie dann die restlichen Zeilen auf der Grundlage dieses Namens aus:

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

Wenn Sie es bevorzugen, verwenden Sie MIN(id) um den ersten Datensatz für jeden Namen zu erhalten, anstatt den letzten.

Es kann auch mit einer INNER JOIN gegen die Unterabfrage. Zu diesem Zweck sollte die Leistung ähnlich sein, und manchmal müssen Sie join on zwei Spalten aus der Unterabfrage.

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

Answer 2

Das Problem ist, dass "distinct" für den gesamten Rückgabesatz gilt und nicht nur für das erste Feld. Sonst wüsste MySQL nicht, welchen Datensatz es zurückgeben soll. Sie benötigen also eine Art Gruppenfunktion für die Bewertung, sei es MAX, MIN, GROUP_CONCAT, AVG oder eine andere Funktion.

Michael hat bereits eine gute Antwort gegeben, daher werde ich die Anfrage nicht noch einmal schreiben.

Answer 3

Sie können eine GROUP BY Klausel:

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

Diese Abfrage ist bei großen Datensätzen leistungsfähiger als die alternativen Unterabfragen und kann auch einfacher zu lesen sein.

Answer 4

Ich stimme mit @rcdmk überein. Die Verwendung einer DEPENDENT-Subquery kann die Leistung beeinträchtigen, GROUP BY scheint besser geeignet zu sein, vorausgesetzt, Sie haben bereits INDEXed die Land und nur wenige Zeilen werden den Server erreichen. Umschreiben der Abfrage giben von @rcdmk , fügte ich die ORDER BY NULL Klausel, um die implizite Sortierung durch GROUP BY zu unterdrücken, damit es etwas schneller geht:

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