Datensätze mit maximalem Wert für jede Gruppe gruppierter SQL-Ergebnisse abrufen

Wie kommst du auf die Zeilen der max-Wert für jede Gruppierung?

Ich habe gesehen, einige übermäßig komplizierte Varianten auf diese Frage, und keines mit einer guten Antwort. Ich habe versucht zusammen zu stellen die einfachste mögliche Beispiel:

Gegeben eine Tabelle wie unten, mit dem person, Gruppe, Alter und Spalten, wie würden Sie die älteste person in jeder Gruppe? (Ein Band innerhalb einer Gruppe sollten die ersten alphabetischer Folge)

Person | Group | Age
---
Bob  | 1     | 32  
Jill | 1     | 34  
Shawn| 1     | 42  
Jake | 2     | 29  
Paul | 2     | 36  
Laura| 2     | 39

Gewünschte Ergebnis einstellen:

Shawn | 1     | 42    
Laura | 2     | 39

InformationsquelleAutor der Frage Yarin | 2012-08-24

115

Gibt es eine super-einfache Möglichkeit, dies zu tun in mysql:
```
select * 
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`
```
Dies funktioniert, da in mysql darfst du dich nicht aggregierte nicht-group-Spalten, in dem Fall mysql nur die Renditen der ersten Zeile. Die Lösung ist die erste, um die Daten so, dass für jede Gruppe die Zeile, die Sie wollen, ist die erste, dann die Gruppe von Spalten, die Sie möchten, dass der Wert für.

Vermeiden Sie komplizierte Unterabfragen, die versuchen zu finden, die max() etc, und auch die Probleme, die die Rückkehr mehrere Zeilen, wenn es mehr als eine mit der gleichen maximalen Wert (wie die anderen Antworten tun würde)

Hinweis: Dies ist ein mysql-nur Lösung. Alle anderen Datenbanken, die ich kenne, werfen Sie einen SQL-syntax-Fehler mit der Meldung "nicht aggregierte Spalten sind nicht in der group by-Klausel" oder so ähnlich. Da diese Lösung verwendet undokumentierte Verhalten, die vorsichtiger aufnehmen wollen, ein test, um zu behaupten, dass es bleibt arbeiten sollte eine zukünftige version von MySQL, dieses Verhalten zu ändern.

Version 5.7 aktualisiert:

Seit der version 5.7, die sql-Modus Einstellung enthält ONLY_FULL_GROUP_BY standardmäßig, so diese Arbeit zu machen müssen Sie nicht haben diese option ("Bearbeiten" die option Datei für den server so entfernen Sie diese Einstellung).

InformationsquelleAutor der Antwort Bohemian
207

Die richtige Lösung ist:
```
SELECT o.*
FROM `Persons` o                    # 'o' from 'oldest person in group'
  LEFT JOIN `Persons` b             # 'b' from 'bigger age'
      ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL                 # bigger age not found
```
Wie es funktioniert:

Es stimmt jede Zeile von o mit allen Zeilen aus b mit dem gleichen Wert in der Spalte Group und einen größeren Wert in der Spalte Age. Jede Zeile von o nicht mit dem maximalen Wert seiner Gruppe in Spalte Age wird entsprechend eine oder mehrere Zeilen aus b.

Den LEFT JOIN macht es die älteste person in der Gruppe (einschließlich der Personen, die alleine in Ihrer Gruppe) mit einer Zeile voller NULLs aus b ('keine größte Alter in der Gruppe").

Mit INNER JOIN lässt diese Reihen, die nicht zusammenpassen und Sie werden ignoriert.

Den WHERE Klausel hält nur die Zeilen mit NULLs in den Felder extrahiert aus b. Sie sind die ältesten Personen aus jeder Gruppe.

Weitere Lesungen

Diese Lösung und viele andere sind erklärt in dem Buch SQL-Antipatterns: Vermeidung der Gefahren von Datenbank-Programmierung

InformationsquelleAutor der Antwort axiac
25

Meine einfache Lösung für SQLite (und wahrscheinlich MySQL):
```
SELECT *, MAX(age) FROM mytable GROUP BY `Group`;
```
Aber es funktioniert nicht in einer PostgreSQL und vielleicht einige andere Plattformen.

In PostgreSQL können Sie UNTERSCHIEDLICHE AUF Klausel:
```
SELECT DISTINCT ON ("group") * FROM "mytable" ORDER BY "group", "age" DESC;
```
InformationsquelleAutor der Antwort Igor Kulagin

Dem man beitreten kann gegen eine Unterabfrage, zieht die MAX(Group) und Age. Diese Methode ist tragbar in den meisten RDBMS.

SELECT
  yourtable.*
FROM
  yourtable
  JOIN (
    SELECT `Group`, MAX(Age) AS age
    FROM yourtable 
    GROUP BY `Group`
  ) maxage
     /* join subquery against both Group and Age values */
     ON yourtable.`Group` = maxage.`Group` 
        AND yourtable.Age = maxage.age

InformationsquelleAutor der Antwort Michael Berkowski

Mithilfe von ranking-Methode.

SELECT @rn :=  CASE WHEN @prev_grp <> groupa THEN 1 ELSE @rn+1 END AS rn,  
   @prev_grp :=groupa,
   person,age,groupa  
FROM   users,(SELECT @rn := 0) r        
HAVING rn=1
ORDER  BY groupa,age DESC,person

InformationsquelleAutor der Antwort sel

2

axiac die Lösung ist, was funktioniert am besten für mich am Ende. Ich hatte eine zusätzliche Komplexität jedoch: ein berechnetes "max-Wert", abgeleitet von zwei Spalten.

Verwenden wir das gleiche Beispiel: ich möchte die älteste person in jeder Gruppe. Wenn es Menschen gibt, die sind gleich alt, nehmen die höchste person.

Ich musste das left join zwei mal zu bekommen ist dieses Verhalten:
```
SELECT o1.* WHERE
    (SELECT o.*
    FROM `Persons` o
    LEFT JOIN `Persons` b
    ON o.Group = b.Group AND o.Age < b.Age
    WHERE b.Age is NULL) o1
LEFT JOIN
    (SELECT o.*
    FROM `Persons` o
    LEFT JOIN `Persons` b
    ON o.Group = b.Group AND o.Age < b.Age
    WHERE b.Age is NULL) o2
ON o1.Group = o2.Group AND o1.Height < o2.Height 
WHERE o2.Height is NULL;
```
Hoffe, das hilft! Ich denke, es sollte eine bessere Möglichkeit, dies zu tun, obwohl...

InformationsquelleAutor der Antwort Arthur C

Allgemeine Tabellenausdrücke Verwenden - Common Table Expressions:

WITH MyCTE(MaxPKID, SomeColumn1)
AS(
SELECT MAX(a.MyTablePKID) AS MaxPKID, a.SomeColumn1
FROM MyTable1 a
GROUP BY a.SomeColumn1
  )
SELECT b.MyTablePKID, b.SomeColumn1, b.SomeColumn2 MAX(b.NumEstado)
FROM MyTable1 b
INNER JOIN MyCTE c ON c.MaxPKID = b.MyTablePKID
GROUP BY b.MyTablePKID, b.SomeColumn1, b.SomeColumn2

--Note: MyTablePKID is the PrimaryKey of MyTable

InformationsquelleAutor der Antwort Marvin

1

Meine Lösung funktioniert nur, wenn Sie müssen abrufen, die nur eine Spalte, aber für meine Bedürfnisse war die beste Gefundene Lösung im Hinblick auf performance (nutze es nur eine einzige Abfrage!):
```
SELECT SUBSTRING_INDEX(GROUP_CONCAT(column_x ORDER BY column_y),',',1) AS xyz,
   column_z
FROM table_name
GROUP BY column_z;
```
Es mit GROUP_CONCAT, um eine geordnete concat Liste und dann habe ich substring nur das erste.

InformationsquelleAutor der Antwort Antonio Giovanazzi

Können Sie auch versuchen,

SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ;

InformationsquelleAutor der Antwort Ritwik

0

Diese Methode hat den Vorteil, dass Sie die Rangfolge nach einer anderen Spalte, und nicht wegwerfen, die anderen Daten. Es ist sehr nützlich in einer situation, wo Sie versuchen, die Liste der Bestellungen mit einer Spalte für Elemente, die Auflistung der schwersten zuerst.

Quelle: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
```
SELECT person, group,
    GROUP_CONCAT(
        DISTINCT age
        ORDER BY age DESC SEPARATOR ', follow up: '
    )
FROM sql_table
GROUP BY group;
```
InformationsquelleAutor der Antwort Ray Foss

Nicht sicher, ob MySQL hat row_number-Funktion. Wenn ja, können Sie es verwenden, um das gewünschte Ergebnis zu erhalten. Auf SQL Server, die Sie tun können, etwas ähnliches wie:

CREATE TABLE p
(
 person NVARCHAR(10),
 gp INT,
 age INT
);
GO
INSERT  INTO p
VALUES  ('Bob', 1, 32);
INSERT  INTO p
VALUES  ('Jill', 1, 34);
INSERT  INTO p
VALUES  ('Shawn', 1, 42);
INSERT  INTO p
VALUES  ('Jake', 2, 29);
INSERT  INTO p
VALUES  ('Paul', 2, 36);
INSERT  INTO p
VALUES  ('Laura', 2, 39);
GO

SELECT  t.person, t.gp, t.age
FROM    (
         SELECT *,
                ROW_NUMBER() OVER (PARTITION BY gp ORDER BY age DESC) row
         FROM   p
        ) t
WHERE   t.row = 1;

InformationsquelleAutor der Antwort user130268

lassen Sie den Tabellennamen, werden die Menschen

select O.*              -- > O for oldest table
from people O , people T
where O.grp = T.grp and 
O.Age = 
(select max(T.age) from people T where O.grp = T.grp
  group by T.grp)
group by O.grp;

InformationsquelleAutor der Antwort user3475425

Wenn ID(und alle coulmns) benötigt, die von mytable

SELECT
    *
FROM
    mytable
WHERE
    id NOT IN (
        SELECT
            A.id
        FROM
            mytable AS A
        JOIN mytable AS B ON A. GROUP = B. GROUP
        AND A.age < B.age
    )

InformationsquelleAutor der Antwort mayank kumar

0

Dies ist, wie ich immer bin N max Zeilen pro Gruppe in mysql
```
SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE  co.country = ci.country AND co.id < ci.id
) < 1
;
```
wie es funktioniert:
- self-join auf die Tabelle
- Gruppen sind durch co.country = ci.country
- N Elemente pro Gruppe kontrolliert werden, indem ) < 1 so für 3 Elemente ) < 3
- um max oder min ist abhängig von: co.id < ci.id
  - co.id < ci.id - max
  - co.id > ci.id - min
Vollständiges Beispiel hier:

mysql select n max-Werte pro Gruppe

InformationsquelleAutor der Antwort Vanko

Habe ich eine einfache Lösung mit WHERE IN

SELECT a.* FROM `mytable` AS a    
WHERE a.age IN( SELECT MAX(b.age) AS age FROM `mytable` AS b GROUP BY b.group )    
ORDER BY a.group ASC, a.person ASC

InformationsquelleAutor der Antwort Khalid Musa Sagar

-1

with CTE as 
(select Person, 
[Group], Age, RN= Row_Number() 
over(partition by [Group] 
order by Age desc) 
from yourtable)`


`select Person, Age from CTE where RN = 1`

InformationsquelleAutor der Antwort Harshad C

-1

Würde ich nicht verwenden-Gruppe als Spalte namens, da es reserviertes Wort ist. Jedoch mit SQL funktionieren würde.

SELECT a.Person, a.Group, a.Age FROM [TABLE_NAME] a
INNER JOIN 
(
  SELECT `Group`, MAX(Age) AS oldest FROM [TABLE_NAME] 
  GROUP BY `Group`
) b ON a.Group = b.Group AND a.Age = b.oldest

InformationsquelleAutor der Antwort Bae Cheol Shin

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.

Version 5.7 aktualisiert:

Wie es funktioniert:

Weitere Lesungen