mysql spalten SQL wählt nur Zeilen mit maximalem Wert für eine Spalte aus




sql zeile mit höchstem wert ausgeben (24)

Diese Lösung macht nur eine Auswahl von YourTable, daher ist es schneller. Es funktioniert nur für MySQL und SQLite (für SQLite entfernen DESC) nach Test auf sqlfiddle.com. Vielleicht kann es optimiert werden, um an anderen Sprachen zu arbeiten, mit denen ich nicht vertraut bin.

SELECT *
FROM ( SELECT *
       FROM ( SELECT 1 as id, 1 as rev, 'content1' as content
              UNION
              SELECT 2, 1, 'content2'
              UNION
              SELECT 1, 2, 'content3'
              UNION
              SELECT 1, 3, 'content4'
            ) as YourTable
       ORDER BY id, rev DESC
   ) as YourTable
GROUP BY id

Ich habe diese Tabelle für Dokumente (vereinfachte Version hier):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

Wie wähle ich eine Zeile pro ID und nur die größte rev aus?
Mit den obigen Daten sollte das Ergebnis zwei Zeilen enthalten: [1, 3, ...] und [2, 1, ..] . Ich benutze MySQL .

Derzeit verwende ich Prüfungen in der while Schleife, um alte Drehzahlen aus dem Resultset zu erkennen und zu überschreiben. Aber ist dies die einzige Methode, um das Ergebnis zu erreichen? Gibt es keine SQL- Lösung?

Aktualisieren
Wie die Antworten nahelegen, gibt es eine SQL-Lösung und hier eine SQLFiddle-Demo .

Update 2
Ich bemerkte, dass nach dem Hinzufügen der oben genannten SQLFiddle die Rate, mit der die Frage hochgestuft wurde, die Upvote-Rate der Antworten übertroffen hat. Das war nicht die Absicht! Die Geige basiert auf den Antworten, insbesondere der angenommenen Antwort.


Ich bin verblüfft, dass keine Antwort SQL-Fensterfunktionslösung angeboten hat:

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
          FROM YourTable) a
 WHERE a.rank = 1 

Hinzugefügt im SQL-Standard ANSI / ISO Standard SQL: 2003 und später erweitert mit ANSI / ISO Standard SQL: 2008, Fenster- (oder Windowing-) Funktionen sind jetzt bei allen wichtigen Herstellern verfügbar. Es gibt mehr Arten von Rangfunktionen, die verfügbar sind, um mit einem Gleichstandproblem umzugehen: RANK, DENSE_RANK, PERSENT_RANK .


Da dies die populärste Frage in Bezug auf dieses Problem ist, werde ich hier noch eine weitere Antwort darauf posten:

Es sieht so aus, als ob es einen einfacheren Weg gibt (aber nur in MySQL ):

select *
from (select * from mytable order by id, rev desc ) x
group by id

Bitte kreuzen Sie die Antwort des Benutzers Bohemian in dieser Frage an, um so eine prägnante und elegante Antwort auf dieses Problem zu geben.

BEARBEITEN: Obwohl diese Lösung für viele Menschen funktioniert, ist sie auf lange Sicht möglicherweise nicht stabil, da MySQL nicht garantiert, dass die GROUP BY-Anweisung sinnvolle Werte für Spalten zurückgibt, die nicht in der GROUP BY-Liste enthalten sind. Verwenden Sie diese Lösung auf eigene Gefahr


Ich verwende gerne eine NOT EXIST basierte Lösung für dieses Problem:

SELECT id, rev
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)

Das funktioniert für mich in sqlite3:

SELECT *, MAX(rev) FROM t1 GROUP BY id

Mit * erhalten Sie eine doppelte rev-Spalte, aber das ist kein großes Problem.


Eine weitere Möglichkeit, den Job auszuführen, ist die Verwendung der analytischen Funktion MAX () in der OVER PARTITION-Klausel

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,MAX(rev) OVER (PARTITION BY id) as max_rev
      FROM YourTable
    ) t
  WHERE t.rev = t.max_rev 

Die andere OVER PARTITION-Lösung, die bereits in diesem Post dokumentiert ist, ist

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
      FROM YourTable
    ) t
  WHERE t.rank = 1 

Diese 2 SELECT funktionieren gut auf Oracle 10g.


select * from yourtable
group by id
having rev=max(rev);

SELECT * FROM Angestellter, wo Employee.Salary in (Wählen Sie max (Gehalt) von Employee group nach Employee_id) ORDER BY Employee.Salary


NICHT mySQL , aber für andere Leute, die diese Frage finden und SQL verwenden, ist eine andere Möglichkeit, das Problem der greatest-n-per-group lösen, Cross Apply in MS SQL

WITH DocIds AS (SELECT DISTINCT id FROM docs)

SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
  SELECT Top 1 * FROM docs d
  WHERE d.id = d1.id
  ORDER BY rev DESC
) d2

Hier ist ein Beispiel in SqlFiddle


Ich denke, das ist die einfachste Lösung:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • SELECT *: Alle Felder zurückgeben.
  • FROM Employee: Tabelle gesucht.
  • (SELECT * ...) Unterabfrage: Zurückgeben aller Personen, sortiert nach Gehalt.
  • GROUP BY employeesub.Salary:: Erzwingt die Top-sortierte Gehaltszeile jedes Mitarbeiters als zurückgegebenes Ergebnis.

Wenn Sie nur die eine Zeile benötigen, ist es noch einfacher:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

Ich denke auch, dass es am einfachsten ist, zu anderen Zwecken zu brechen, zu verstehen und zu modifizieren:

  • ORDER BY Employee.Salary DESC: Ordnen Sie die Ergebnisse nach dem Gehalt an, mit den höchsten Gehältern zuerst.
  • LIMIT 1: Gibt nur ein Ergebnis zurück.

Diesen Ansatz zu verstehen und all diese ähnlichen Probleme zu lösen, wird trivial: Mitarbeiter mit dem niedrigsten Gehalt bekommen (DESC in ASC ändern), Top-Ten-Mitarbeiter verdienen (LIMIT 1 zu LIMIT 10 ändern), nach einem anderen Feld sortieren (ORDER BY ändern) Employee.Salary zu ORDER BY Employee.Commission), etc ..


Eine weitere Lösung besteht darin, eine korrelierte Unterabfrage zu verwenden:

select yt.id, yt.rev, yt.contents
    from YourTable yt
    where rev = 
        (select max(rev) from YourTable st where yt.id=st.id)

Ein Index auf (id, rev) macht die Unterabfrage fast wie eine einfache Suche ...

Es folgen Vergleiche mit den Lösungen in @ AdrianCarneiros Antwort (Unterabfrage, linksbündig), basierend auf MySQL-Messungen mit der InnoDB-Tabelle von ~ 1 Millionen Datensätzen, wobei die Gruppengröße: 1-3 ist.

Während für vollständige Tabellen-Scans Unterabfrage / linksbündig / korrelierte Zeiten miteinander in Beziehung stehen als 6/8/9, wenn es um direkte Suchvorgänge oder Stapel ( id in (1,2,3) ) geht, ist die Unterabfrage viel langsamer als die anderen ( Durch erneutes Ausführen der Unterabfrage). Jedoch konnte ich nicht zwischen linksbündiger und korrelierter Lösung in der Geschwindigkeit unterscheiden.

Eine letzte Note, als leftjoin erzeugt n * (n + 1) / 2 Joins in Gruppen, deren Performance stark von der Größe der Gruppen beeinflusst werden kann ...


Ich habe das Folgende benutzt, um ein eigenes Problem zu lösen. Ich habe zuerst eine temporäre Tabelle erstellt und den maximalen rev-Wert pro eindeutiger ID eingefügt.

CREATE TABLE #temp1
(
    id varchar(20)
    , rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM 
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as a 
GROUP BY a.id
ORDER BY a.id

Ich verband dann diese Maximalwerte (# temp1) mit allen möglichen ID / Inhalt-Kombinationen. Indem ich dies tue, filtere ich natürlich die nicht-maximalen ID / Inhalt-Kombinationen heraus und belasse nur die einzigen maximalen rev-Werte für jeden.

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id

Ich kann mich nicht für die Leistung verbürgen, aber hier ist ein Trick, der von den Einschränkungen von Microsoft Excel inspiriert ist. Es hat einige gute Eigenschaften

GUTES ZEUG

  • Es sollte die Rückkehr von nur einem "Max Record" erzwingen, auch wenn es eine Gleichheit gibt (manchmal nützlich)
  • Es erfordert keinen Beitritt

ANSATZ

Es ist ein bisschen hässlich und erfordert, dass Sie etwas über den Bereich der gültigen Werte der Rev- Spalte wissen. Nehmen wir an, wir wissen, dass die rev- Spalte eine Zahl zwischen 0.00 und 999 einschließlich Dezimalstellen ist, dass aber immer nur zwei Stellen rechts vom Dezimalpunkt stehen (z. B. 34.17 wäre ein gültiger Wert).

Der Kern der Sache ist, dass Sie eine einzelne synthetische Spalte durch Verketten / Verpacken des primären Vergleichsfeldes zusammen mit den gewünschten Daten erstellen. Auf diese Weise können Sie die SQL-Funktion MAX () zwingen, alle Daten zurückzugeben (weil sie in eine einzelne Spalte gepackt wurde). Dann müssen Sie die Daten entpacken.

So sieht es mit dem obigen Beispiel aus, geschrieben in SQL

SELECT id, 
       CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
       SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev 
FROM  (SELECT id, 
       CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
       FROM yourtable
      ) 
GROUP BY id

Das Packen beginnt damit, dass die rev- Spalte unabhängig vom Wert von rev zum Beispiel eine Anzahl bekannter Zeichenlängen ist

  • 3.2 wird 1003.201
  • 57 wird 1057.001
  • 923.88 wird 1923.881

Wenn Sie es richtig machen, sollte der String-Vergleich zweier Zahlen das gleiche "max" ergeben wie der numerische Vergleich der beiden Zahlen und es ist einfach, mit der Teilstring-Funktion (die in der einen oder anderen Form verfügbar ist) zurück in die ursprüngliche Zahl zu konvertieren überall).


SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

Wenn jemand nach einer Linq-Version sucht, scheint dies für mich zu funktionieren:

public static IQueryable<BlockVersion> LatestVersionsPerBlock(this IQueryable<BlockVersion> blockVersions)
{
    var max_version_per_id = blockVersions.GroupBy(v => v.BlockId)
        .Select( v => new { BlockId = v.Key, MaxVersion = v.Max(x => x.Version) } );    

    return blockVersions.Where( v => max_version_per_id.Any(x => x.BlockId == v.BlockId && x.MaxVersion == v.Version) );
}

Hier ist eine nette Art, das zu tun

Verwenden Sie folgenden Code:

with temp as  ( 
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)

Wenn Sie viele Felder in der SELECT-Anweisung haben und den letzten Wert für alle diese Felder durch optimierten Code erhalten möchten:

select * from
(select * from table_name
order by id,rev desc) temp
group by id 

Viele, wenn nicht alle der anderen Antworten sind für kleine Datensätze geeignet. Für die Skalierung ist mehr Vorsicht geboten. Siehe here .

Es beschreibt mehrere schnellere Möglichkeiten, gruppenweise max und top-N pro Gruppe auszuführen.


Auf den ersten Blick...

Sie benötigen GROUP BY eine GROUP BY Klausel mit der MAX Aggregatfunktion:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

Es ist nie so einfach, oder?

Ich habe gerade bemerkt, dass du auch die content brauchst.

Dies ist eine sehr häufige Frage in SQL: Finde die ganzen Daten für die Zeile mit einem maximalen Wert in einer Spalte für eine Gruppenidentifizierung. Das habe ich während meiner Karriere oft gehört. Eigentlich war es eine der Fragen, die ich im technischen Interview meines aktuellen Jobs beantwortet habe.

Tatsächlich ist es so üblich, dass die -Community ein einziges Tag erstellt hat, um sich mit Fragen wie dieser zu befassen: greatest-n-per-group .

Im Grunde haben Sie zwei Ansätze, um dieses Problem zu lösen:

Beitritt mit einfacher group-identifier, max-value-in-group Sub-Abfrage

Bei diesem Ansatz finden Sie zuerst den group-identifier, max-value-in-group (oben bereits gelöst) in einer Unterabfrage. Dann verbinden Sie Ihre Tabelle mit der Unterabfrage mit Gleichheit auf group-identifier und max-value-in-group :

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Linke Join mit Self, Tweaking Join Bedingungen und Filter

Bei dieser Vorgehensweise verlassen Sie die Tabelle mit sich selbst. Gleichheit geht natürlich in der group-identifier . Dann, 2 intelligente Bewegungen:

  1. Die zweite Join-Bedingung hat den linken Seitenwert kleiner als den rechten Wert
  2. Wenn Sie Schritt 1 ausführen, wird die Zeile (n), die tatsächlich den Maximalwert hat, auf der rechten Seite NULL haben (es handelt sich um einen LEFT JOIN , erinnern Sie sich?). Dann filtern wir das verbundene Ergebnis und zeigen nur die Zeilen an, deren rechte Seite NULL .

Also hast du am Ende:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Fazit

Beide Ansätze bringen genau das gleiche Ergebnis.

Wenn Sie zwei Zeilen mit dem group-identifier max-value-in-group , sind beide Zeilen im Ergebnis beider Ansätze enthalten.

Beide Ansätze sind SQL ANSI-kompatibel und funktionieren daher mit Ihrem bevorzugten RDBMS, unabhängig von seinem "Geschmack".

Beide Ansätze sind auch leistungsfreundlich, jedoch kann Ihre Laufleistung variieren (RDBMS, DB-Struktur, Indizes usw.). Also, wenn Sie einen Ansatz gegenüber dem anderen wählen, Benchmark . Und stellen Sie sicher, dass Sie diejenige auswählen, die für Sie am sinnvollsten ist.


Keine dieser Antworten hat für mich funktioniert.

Das hat bei mir funktioniert.

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max

Etwas wie das?

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev FROM yourtable
    WHERE yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)

Ich bevorzuge so wenig Code wie möglich ...

Du kannst es mit IN versuchen:

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

Für mich ist es weniger kompliziert ... einfacher zu lesen und zu warten.


Hier ist eine andere Lösung hoffe, es wird jemandem helfen

Select a.id , a.rev, a.content from Table1 a
inner join 
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev

Eine dritte Lösung, die ich kaum jemals erwähnt habe, ist MySQL-spezifisch und sieht so aus:

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

Ja, es sieht schrecklich aus (Umwandlung in String und zurück usw.), aber nach meiner Erfahrung ist es normalerweise schneller als die anderen Lösungen. Vielleicht nur für meine Anwendungsfälle, aber ich habe es auf Tabellen mit Millionen von Datensätzen und vielen einzigartigen IDs verwendet. Vielleicht liegt es daran, dass MySQL ziemlich schlecht darin ist, die anderen Lösungen zu optimieren (zumindest in den 5.0 Tagen, als ich mit dieser Lösung aufkam).

Eine wichtige Sache ist, dass GROUP_CONCAT eine maximale Länge für die Zeichenfolge hat, die es aufbauen kann. Wahrscheinlich möchten Sie dieses Limit erhöhen, indem Sie die Variable group_concat_max_len . Beachten Sie, dass dies eine Skalierungsgrenze darstellt, wenn Sie eine große Anzahl von Zeilen haben.

Wie auch immer, das obige funktioniert nicht direkt, wenn Ihr Inhaltsfeld bereits Text ist. In diesem Fall möchten Sie wahrscheinlich ein anderes Trennzeichen verwenden, wie zB \ 0. Sie werden auch schneller auf das group_concat_max_len Limit group_concat_max_len .





greatest-n-per-group