wake-up-neo.com

Rufen Sie Datensätze mit maximalem Wert für jede Gruppe gruppierter SQL-Ergebnisse ab

Wie erhalten Sie die Zeilen, die den Maximalwert für jede gruppierte Gruppe enthalten? 

Ich habe einige zu komplizierte Variationen dieser Frage gesehen und keine mit einer guten Antwort. Ich habe versucht, ein möglichst einfaches Beispiel zusammenzustellen:

Wie würden Sie in einer solchen Tabelle mit Spalten für Personen, Gruppen und Alter die älteste Person in jeder Gruppe ermitteln? (Ein Unentschieden innerhalb einer Gruppe sollte das erste alphabetische Ergebnis ergeben.)

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

Gewünschte Ergebnismenge: 

Shawn | 1     | 42    
Laura | 2     | 39  
177
Yarin

Es gibt eine sehr einfache Möglichkeit, dies in mysql zu tun:

select * 
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

Dies funktioniert, weil Sie in mysql not Nicht-Gruppenspalten sammeln können. In diesem Fall gibt mysql nur die Zeile first zurück. Die Lösung besteht darin, die Daten zuerst so zu ordnen, dass für jede Gruppe zuerst die gewünschte Zeile zuerst angezeigt wird, und dann nach den Spalten gruppiert, für die der Wert gewünscht wird.

Sie vermeiden komplizierte Unterabfragen, die versuchen, max() etc zu finden, und auch die Probleme, mehrere Zeilen zurückzugeben, wenn es mehr als eine mit demselben Maximalwert gibt (wie die anderen Antworten dies tun würden).

Hinweis: Dies ist eine Lösung von nur MySQL. Alle anderen Datenbanken, von denen ich weiß, werden einen SQL-Syntaxfehler mit der Meldung "Nicht aggregierte Spalten werden in der Klausel group by" oder ähnliches ausgegeben. Da diese Lösung undokumentiertes-Verhalten verwendet, kann der vorsichtige Benutzer einen Test hinzufügen, um zu bestätigen, dass (bleibt _ funktioniert, falls eine zukünftige Version von MySQL dieses Verhalten ändert.

Version 5.7 Update:

Seit Version 5.7 enthält die Einstellung sql-mode standardmäßig ONLY_FULL_GROUP_BY . Damit dies funktioniert, müssen Sie nicht über diese Option verfügen (bearbeiten Sie die Optionsdatei für den Server, um diese Einstellung zu entfernen.) .

125
Bohemian

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 mit jeder Zeile von o überein, wobei alle Zeilen von b denselben Wert in Spalte Group und einen größeren Wert in Spalte Age haben. Jede Zeile aus o, die nicht den Maximalwert ihrer Gruppe in Spalte Age hat, stimmt mit einer oder mehreren Zeilen von b überein.

Der LEFT JOIN macht es zur ältesten Person in der Gruppe (einschließlich der Personen, die alleine in ihrer Gruppe sind) mit einer Reihe von NULLs von b ("kein größtes Alter in der Gruppe").
Wenn Sie INNER JOIN verwenden, stimmen diese Zeilen nicht überein und werden ignoriert.

Die WHERE-Klausel behält nur die Zeilen mit NULLs in den Feldern bei, die aus b extrahiert wurden. Sie sind die ältesten Personen aus jeder Gruppe.

Weitere Lesungen

Diese und viele andere Lösungen werden im Buch SQL Antipatterns: Vermeiden der Fallstricke der Datenbankprogrammierung

235
axiac

Sie können einer Unterabfrage beitreten, die MAX(Group) und Age abzieht. Diese Methode ist in den meisten RDBMS portierbar.

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT `Group`, MAX(Age) AS max_age
    FROM yourTable
    GROUP BY `Group`
) t2
    ON t1.`Group` = t2.`Group` AND t1.Age = t2.max_age;
31

Meine einfache Lösung für SQLite (und wahrscheinlich MySQL):

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

Es funktioniert jedoch nicht in PostgreSQL und möglicherweise einigen anderen Plattformen.

In PostgreSQL können Sie die DISTINCT ON -Klausel verwenden:

SELECT DISTINCT ON ("group") * FROM "mytable" ORDER BY "group", "age" DESC;
27
Igor Kulagin

Ranking-Methode verwenden.

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
3
sel

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

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;
2
user130268

die Lösung von axiac hat am Ende für mich am besten funktioniert. Ich hatte jedoch eine zusätzliche Komplexität: einen berechneten "Maximalwert", der aus zwei Spalten abgeleitet wurde.

Nehmen wir das gleiche Beispiel: Ich möchte die älteste Person in jeder Gruppe. Wenn es Menschen gibt, die gleich alt sind, nimm die größte Person.

Ich musste den linken Join zweimal ausführen, um dieses Verhalten zu erhalten:

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 einen besseren Weg geben, dies zu tun ...

2
Arthur C

Meine Lösung funktioniert nur, wenn Sie nur eine Spalte abrufen möchten. Für meine Anforderungen wurde jedoch die beste Lösung in Bezug auf die Leistung gefunden (es wird nur eine einzige Abfrage verwendet!):

SELECT SUBSTRING_INDEX(GROUP_CONCAT(column_x ORDER BY column_y),',',1) AS xyz,
   column_z
FROM table_name
GROUP BY column_z;

Sie verwenden GROUP_CONCAT, um eine geordnete Concat-Liste zu erstellen, und dann nur noch die erste.

1

CTEs verwenden - Allgemeine Tabellenausdrücke:

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
1
Marvin

Ich würde Group nicht als Spaltennamen verwenden, da es reserviertes Word ist. Allerdings würde folgendes SQL funktionieren.

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
0
Bae Cheol Shin

So bekomme ich die 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:

  • selbst mit dem Tisch verbinden
  • gruppen werden von co.country = ci.country erstellt
  • N Elemente pro Gruppe werden durch ) < 1 gesteuert, also für 3 Elemente -) <3
  • max oder min zu erhalten hängt von folgenden Faktoren ab: co.id < ci.id
    • co.id <ci.id - max
    • co.id> ci.id - min

Vollständiges Beispiel hier:

MySQL-Auswahl n max Werte pro Gruppe

0
Vanko
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`
0
Harshad

Wenn ID (und alle Spalten) von mytable benötigt wird 

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
    )
0
mayank kumar

Diese Methode hat den Vorteil, dass Sie nach einer anderen Spalte sortieren können und die anderen Daten nicht verworfen werden. Dies ist in einer Situation sehr nützlich, in der Sie Bestellungen mit einer Spalte für Artikel auflisten und die schwersten zuerst auflisten.

Quelle: http://dev.mysql.com/doc/refman/5.0/de/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;
0
Ray Foss

Ich habe 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
0

lass den tischnamen menschen sein

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; 
0
user3475425

Sie können es auch versuchen

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