Wie kann ich SELECT Zeilen mit MAX(Spaltenwert), PARTITION durch eine andere Spalte in MYSQL?

Question

Mein Tisch ist:

id

Startseite

datetime

Spieler

Ressource

1

10

04/03/2009

john

399

2

11

04/03/2009

juliet

244

5

12

04/03/2009

borat

555

3

10

03/03/2009

john

300

4

11

03/03/2009

juliet

200

6

12

03/03/2009

borat

500

7

13

24/12/2008

borat

600

8

13

01/01/2009

borat

700

Ich muss jedes einzelne Element auswählen home die den maximalen Wert von datetime .

Das Ergebnis wäre:

id

Startseite

datetime

Spieler

Ressource

1

10

04/03/2009

john

399

2

11

04/03/2009

juliet

244

5

12

04/03/2009

borat

555

8

13

01/01/2009

borat

700

Ich habe es versucht:

-- 1 ..by the MySQL manual: 

SELECT DISTINCT
  home,
  id,
  datetime AS dt,
  player,
  resource
FROM topten t1
WHERE datetime = (SELECT
  MAX(t2.datetime)
FROM topten t2
GROUP BY home)
GROUP BY datetime
ORDER BY datetime DESC

Funktioniert nicht. Die Ergebnismenge hat 130 Zeilen, obwohl die Datenbank 187 Zeilen enthält. Das Ergebnis enthält einige Duplikate von home .

-- 2 ..join

SELECT
  s1.id,
  s1.home,
  s1.datetime,
  s1.player,
  s1.resource
FROM topten s1
JOIN (SELECT
  id,
  MAX(datetime) AS dt
FROM topten
GROUP BY id) AS s2
  ON s1.id = s2.id
ORDER BY datetime 

Nö. Es gibt alle Aufzeichnungen.

-- 3 ..something exotic: 

Mit unterschiedlichen Ergebnissen.

Answer 1

Hier ist die MySQL-Version, die nur einen Eintrag ausgibt, wenn es Duplikate MAX(datetime) in einer Gruppe gibt.

Sie können hier testen http://www.sqlfiddle.com/#!2/0a4ae/1

Beispielhafte Daten

mysql> SELECT * from topten;
+------+------+---------------------+--------+----------+
| id   | home | datetime            | player | resource |
+------+------+---------------------+--------+----------+
|    1 |   10 | 2009-04-03 00:00:00 | john   |      399 |
|    2 |   11 | 2009-04-03 00:00:00 | juliet |      244 |
|    3 |   10 | 2009-03-03 00:00:00 | john   |      300 |
|    4 |   11 | 2009-03-03 00:00:00 | juliet |      200 |
|    5 |   12 | 2009-04-03 00:00:00 | borat  |      555 |
|    6 |   12 | 2009-03-03 00:00:00 | borat  |      500 |
|    7 |   13 | 2008-12-24 00:00:00 | borat  |      600 |
|    8 |   13 | 2009-01-01 00:00:00 | borat  |      700 |
|    9 |   10 | 2009-04-03 00:00:00 | borat  |      700 |
|   10 |   11 | 2009-04-03 00:00:00 | borat  |      700 |
|   12 |   12 | 2009-04-03 00:00:00 | borat  |      700 |
+------+------+---------------------+--------+----------+

MySQL-Version mit Benutzervariable

SELECT *
FROM (
    SELECT ord.*,
        IF (@prev_home = ord.home, 0, 1) AS is_first_appear,
        @prev_home := ord.home
    FROM (
        SELECT t1.id, t1.home, t1.player, t1.resource
        FROM topten t1
        INNER JOIN (
            SELECT home, MAX(datetime) AS mx_dt
            FROM topten
            GROUP BY home
          ) x ON t1.home = x.home AND t1.datetime = x.mx_dt
        ORDER BY home
    ) ord, (SELECT @prev_home := 0, @seq := 0) init
) y
WHERE is_first_appear = 1;
+------+------+--------+----------+-----------------+------------------------+
| id   | home | player | resource | is_first_appear | @prev_home := ord.home |
+------+------+--------+----------+-----------------+------------------------+
|    9 |   10 | borat  |      700 |               1 |                     10 |
|   10 |   11 | borat  |      700 |               1 |                     11 |
|   12 |   12 | borat  |      700 |               1 |                     12 |
|    8 |   13 | borat  |      700 |               1 |                     13 |
+------+------+--------+----------+-----------------+------------------------+
4 rows in set (0.00 sec)

Angenommene Antworten' outout

SELECT tt.*
FROM topten tt
INNER JOIN
    (
    SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home
) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime
+------+------+---------------------+--------+----------+
| id   | home | datetime            | player | resource |
+------+------+---------------------+--------+----------+
|    1 |   10 | 2009-04-03 00:00:00 | john   |      399 |
|    2 |   11 | 2009-04-03 00:00:00 | juliet |      244 |
|    5 |   12 | 2009-04-03 00:00:00 | borat  |      555 |
|    8 |   13 | 2009-01-01 00:00:00 | borat  |      700 |
|    9 |   10 | 2009-04-03 00:00:00 | borat  |      700 |
|   10 |   11 | 2009-04-03 00:00:00 | borat  |      700 |
|   12 |   12 | 2009-04-03 00:00:00 | borat  |      700 |
+------+------+---------------------+--------+----------+
7 rows in set (0.00 sec)

Answer 2

SELECT c1, c2, c3, c4, c5 FROM table1 WHERE c3 = (select max(c3) from table)

SELECT * FROM table1 WHERE c3 = (select max(c3) from table1)

Answer 3

Ein anderer Weg, um die neueste Zeile pro Gruppe mit einer Unterabfrage, die im Grunde berechnet einen Rang für jede Zeile pro Gruppe und dann filtern Sie Ihre neuesten Zeilen wie mit Rang = 1 gt

select a.*
from topten a
where (
  select count(*)
  from topten b
  where a.home = b.home
  and a.`datetime` < b.`datetime`
) +1 = 1

DEMO

Hier ist die visuelle Demo für Rangnummer für jede Zeile zum besseren Verständnis

Durch das Lesen einiger Kommentare Was ist, wenn es zwei Zeilen gibt, die die gleichen Werte für die Felder "home" und "datetime" haben?

Die obige Abfrage schlägt fehl und gibt mehr als 1 Zeile für die obige Situation zurück. Um diese Situation abzudecken, wird ein weiteres Kriterium/Parameter/Spalte benötigt, um zu entscheiden, welche Zeile genommen werden soll, die in die obige Situation fällt. Beim Betrachten des Beispieldatensatzes nehme ich an, dass es eine Primärschlüsselspalte gibt id die auf automatische Inkrementierung eingestellt sein sollte. Wir können also diese Spalte verwenden, um die neueste Zeile auszuwählen, indem wir dieselbe Abfrage mit Hilfe von CASE Anweisung wie

select a.*
from topten a
where (
  select count(*)
  from topten b
  where a.home = b.home
  and  case 
       when a.`datetime` = b.`datetime`
       then a.id < b.id
       else a.`datetime` < b.`datetime`
       end
) + 1 = 1

DEMO

Die obige Abfrage wählt die Zeile mit der höchsten ID unter den gleichen datetime Werte

visuelle Demo für Rang Nr. für jede Zeile

Answer 4

In MySQL 8.0 kann dies effizient durch die Verwendung der Fensterfunktion row_number() mit einem allgemeinen Tabellenausdruck erreicht werden.

(Hier erzeugt row_number() im Grunde eine eindeutige Folge für jede Zeile für jeden Spieler, beginnend mit 1 in absteigender Reihenfolge der Ressourcen. Für jeden Spieler wird also die Zeile mit der Sequenznummer 1 den höchsten Ressourcenwert haben. Jetzt müssen wir nur noch die Zeile mit der Sequenznummer 1 für jeden Spieler auswählen. Dies kann durch das Schreiben einer äußeren Abfrage um diese Abfrage herum geschehen. Wir haben jedoch stattdessen den allgemeinen Tabellenausdruck verwendet, da er besser lesbar ist).

Schema:

 create  TABLE TestTable(id INT, home INT, date DATETIME, 
   player VARCHAR(20), resource INT);
 INSERT INTO TestTable
 SELECT 1, 10, '2009-03-04', 'john', 399 UNION
 SELECT 2, 11, '2009-03-04', 'juliet', 244 UNION
 SELECT 5, 12, '2009-03-04', 'borat', 555 UNION
 SELECT 3, 10, '2009-03-03', 'john', 300 UNION
 SELECT 4, 11, '2009-03-03', 'juliet', 200 UNION
 SELECT 6, 12, '2009-03-03', 'borat', 500 UNION
 SELECT 7, 13, '2008-12-24', 'borat', 600 UNION
 SELECT 8, 13, '2009-01-01', 'borat', 700

Abfrage:

 with cte as 
 (
     select id, home, date , player, resource, 
     Row_Number()Over(Partition by home order by date desc) rownumber from TestTable
 )
 select id, home, date , player, resource from cte where rownumber=1

Ausgabe:

id

Startseite

Datum

Spieler

Ressource

1

10

2009-03-04 00:00:00

john

399

2

11

2009-03-04 00:00:00

juliet

244

5

12

2009-03-04 00:00:00

borat

555

8

13

2009-01-01 00:00:00

borat

700

_db<>Gefiedel ici_

Answer 5

Warum nicht verwenden: SELECT home, MAX(datetime) AS MaxDateTime,player,resource FROM topten GROUP BY home Habe ich etwas übersehen?