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
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.
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.) .
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
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 NULL
s 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 NULL
s in den Feldern bei, die aus b
extrahiert wurden. Sie sind die ältesten Personen aus jeder Gruppe.
Diese und viele andere Lösungen werden im Buch SQL Antipatterns: Vermeiden der Fallstricke der Datenbankprogrammierung
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;
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;
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
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;
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 ...
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.
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
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
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:
co.country = ci.country
erstellt) < 1
gesteuert, also für 3 Elemente -) <3co.id < ci.id
Vollständiges Beispiel hier:
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`
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
)
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;
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
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;
Sie können es auch versuchen
SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ;