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

Du bist so nah dran! Alles, was Sie tun müssen, ist Beides, das Haus und die maximale Datumszeit, auszuwählen und sich dann wieder mit der topten Tabelle auf BEIDEN Feldern:

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

Answer 2

Die schnellste MySQL Lösung, ohne innere Abfragen und ohne GROUP BY :

SELECT m.*                    -- get the row that contains the max value
FROM topten m                 -- "m" from "max"
    LEFT JOIN topten b        -- "b" from "bigger"
        ON m.home = b.home    -- match "max" row with "bigger" row by `home`
        AND m.datetime < b.datetime           -- want "bigger" than "max"
WHERE b.datetime IS NULL      -- keep only if there is no bigger than max

Erläuterung :

Verbinden Sie die Tabelle mit sich selbst, indem Sie die home Spalte. Die Verwendung von LEFT JOIN sorgt dafür, dass alle Zeilen der Tabelle m in der Ergebnismenge erscheinen. Diejenigen, die keine Übereinstimmung in der Tabelle haben b haben wird NULL s für die Spalten von b .

Die andere Bedingung für die JOIN bittet darum, nur die Zeilen aus b die einen größeren Wert auf der datetime Spalte als die Zeile aus m .

Anhand der in der Frage angegebenen Daten kann die LEFT JOIN wird diese Paare erzeugen:

+------------------------------------------+--------------------------------+
|              the row from `m`            |    the matching row from `b`   |
|------------------------------------------|--------------------------------|
| id  home  datetime     player   resource | id    home   datetime      ... |
|----|-----|------------|--------|---------|------|------|------------|-----|
| 1  | 10  | 04/03/2009 | john   | 399     | NULL | NULL | NULL       | ... | *
| 2  | 11  | 04/03/2009 | juliet | 244     | NULL | NULL | NULL       | ... | *
| 5  | 12  | 04/03/2009 | borat  | 555     | NULL | NULL | NULL       | ... | *
| 3  | 10  | 03/03/2009 | john   | 300     | 1    | 10   | 04/03/2009 | ... |
| 4  | 11  | 03/03/2009 | juliet | 200     | 2    | 11   | 04/03/2009 | ... |
| 6  | 12  | 03/03/2009 | borat  | 500     | 5    | 12   | 04/03/2009 | ... |
| 7  | 13  | 24/12/2008 | borat  | 600     | 8    | 13   | 01/01/2009 | ... |
| 8  | 13  | 01/01/2009 | borat  | 700     | NULL | NULL | NULL       | ... | *
+------------------------------------------+--------------------------------+

Schließlich ist die WHERE Klausel behält nur die Paare, die NULL s in den Spalten von b (sie sind gekennzeichnet mit * in der obigen Tabelle); dies bedeutet, dass aufgrund der zweiten Bedingung aus der JOIN Klausel, die Zeile, die aus m hat den größten Wert in der Spalte datetime .

Lesen Sie die SQL-Antipatterns: Vermeiden der Fallstricke der Datenbankprogrammierung Buch für weitere SQL-Tipps.

Answer 3

Hier geht's T-SQL Version:

-- Test data
DECLARE @TestTable TABLE (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

-- Answer
SELECT id, home, date, player, resource 
FROM (SELECT id, home, date, player, resource, 
    RANK() OVER (PARTITION BY home ORDER BY date DESC) N
    FROM @TestTable
)M WHERE N = 1

-- and if you really want only home with max date
SELECT T.id, T.home, T.date, T.player, T.resource 
    FROM @TestTable T
INNER JOIN 
(   SELECT TI.id, TI.home, TI.date, 
        RANK() OVER (PARTITION BY TI.home ORDER BY TI.date) N
    FROM @TestTable TI
    WHERE TI.date IN (SELECT MAX(TM.date) FROM @TestTable TM)
)TJ ON TJ.N = 1 AND T.id = TJ.id

EDIT
Leider gibt es in MySQL keine RANK() OVER-Funktion.
Aber es kann nachgeahmt werden, siehe Emulation analytischer (AKA Ranking) Funktionen mit MySQL .
Dies ist also MySQL Version:

SELECT id, home, date, player, resource 
FROM TestTable AS t1 
WHERE 
    (SELECT COUNT(*) 
            FROM TestTable AS t2 
            WHERE t2.home = t1.home AND t2.date > t1.date
    ) = 0

Answer 4

Dies funktioniert auch, wenn Sie zwei oder mehr Zeilen für jede home mit gleichen DATETIME 's:

SELECT id, home, datetime, player, resource
FROM   (
       SELECT (
              SELECT  id
              FROM    topten ti
              WHERE   ti.home = t1.home
              ORDER BY
                      ti.datetime DESC
              LIMIT 1
              ) lid
       FROM   (
              SELECT  DISTINCT home
              FROM    topten
              ) t1
       ) ro, topten t2
WHERE  t2.id = ro.lid

Answer 5

Ich denke, dass Sie damit das gewünschte Ergebnis erzielen werden:

SELECT   home, MAX(datetime)
FROM     my_table
GROUP BY home

BUT wenn Sie auch andere Spalten benötigen, machen Sie einfach eine Verknüpfung mit der Originaltabelle (prüfen Sie Michael La Voie Antwort)

Mit freundlichen Grüßen.