Wie rufe ich die vier neuesten Elemente pro Kategorie mit einer einzigen SQL-Abfrage ab?

Wählen Sie die neuesten Artikel in jeder Kategorie mit einer einzigen Abfrage aus

In einer Datenbank mit Artikeln, die nach einem Feld namens category_id klassifiziert sind, besteht die Aufgabe darin, eine Liste von Kategorien abzurufen, die jeweils die vier zuletzt aufgelisteten Artikel enthalten. Anstatt die Datenbank für jede Kategorie einzeln abzufragen, verwenden Sie eine einzige SQL-Abfrage, um die Datenbankaufrufe zu optimieren.

Lösung mit Outer Join:

Die folgende Abfrage verwendet einen Outer-Join, um Elemente zu identifizieren und auszuschließen, die aktualisierte Gegenstücke in derselben Kategorie haben:

SELECT i1.*
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id)
GROUP BY i1.category_id, i1.item_id
HAVING COUNT(*) <= 4;

Diese Abfrage verwendet LEFT OUTER JOIN, um jedes Element (i1) mit seinem aktualisierten Satz von Elementen (i2) zu verbinden, die dieselbe Kategorie haben. COUNT(*) Wird verwendet, um die Anzahl der Übereinstimmungen für jedes Element in jeder Kategorie zu zählen. Die HAVING-Klausel filtert Elemente mit mehr als vier Übereinstimmungen heraus und stellt so sicher, dass nur die vier neuesten Elemente in jeder Kategorie ausgewählt werden.

Lösung mit MySQL-Benutzervariablen:

Diese Lösung nutzt die Benutzervariablenfunktion von MySQL, um Gruppen- und Zeilennummern zu verfolgen:

SELECT *
FROM (
    SELECT i.*, @r := IF(@g = category_id, @r+1, 1) AS rownum, @g := category_id
    FROM (SELECT @g:=null, @r:=0) AS _init
    CROSS JOIN item i
    ORDER BY i.category_id, date_listed DESC
) AS t
WHERE t.rownum <= 4;

In dieser Abfrage werden die benutzerdefinierten Variablen @g und @r verwendet, um die aktuelle Kategorie und Zeilennummer zu verfolgen und sicherzustellen, dass nur die ersten vier Elemente in jeder Kategorie ausgewählt werden.

Lösung mit MySQL-Fensterfunktionen (MySQL 8.0.3):

MySQL 8.0.3 führt die Unterstützung für SQL-Standardfensterfunktionen ein und bietet so eine präzisere und effizientere Lösung:

WITH numbered_item AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY date_listed DESC) AS rownum
  FROM item
)
SELECT * FROM numbered_item WHERE rownum <= 4;

Diese Abfrage verwendet die PARTITION BY category_id ORDER BY date_listed DESC-Klausel, um die Ergebnismenge nach Kategorie zu partitionieren und die Elemente in absteigender Reihenfolge nach der date_listed-Spalte in jeder Partition zu sortieren. Die Fensterfunktion ROW_NUMBER() weist dann jeder Partition fortlaufende Zeilennummern zu und ermöglicht so die Auswahl der ersten vier Elemente jeder Kategorie.

Das obige ist der detaillierte Inhalt vonWie rufe ich die vier neuesten Elemente pro Kategorie mit einer einzigen SQL-Abfrage ab?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!