Comment récupérer les quatre éléments les plus récents par catégorie avec une seule requête SQL ?

Sélectionnez les derniers éléments de chaque catégorie à l'aide d'une seule requête

Dans une base de données d'éléments classés par un champ nommé category_id, la tâche consiste à récupérer une liste de catégories, chacune contenant ses quatre éléments les plus récemment répertoriés. Plutôt que d'interroger la base de données individuellement pour chaque catégorie, utilisez une seule requête SQL pour optimiser les appels à la base de données.

Solution utilisant la jointure externe :

La requête suivante utilise une jointure externe pour identifier et exclure les éléments qui ont mis à jour leurs homologues dans la même catégorie :

<code class="language-sql">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;</code>

Cette requête utilise LEFT OUTER JOIN pour joindre chaque élément (i1) avec son ensemble d'éléments mis à jour (i2) qui a la même catégorie. COUNT(*) Utilisé pour compter le nombre de correspondances pour chaque élément de chaque catégorie. La clause HAVING filtre les éléments avec plus de quatre correspondances, garantissant que seuls les quatre éléments les plus récents de chaque catégorie sont sélectionnés.

Solution utilisant les variables utilisateur MySQL :

Cette solution utilise la fonctionnalité de variable utilisateur de MySQL pour suivre les numéros de groupe et de ligne :

<code class="language-sql">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;</code>

Dans cette requête, les variables définies par l'utilisateur @g et @r sont utilisées pour garder une trace de la catégorie actuelle et du numéro de ligne, garantissant que seuls les quatre premiers éléments de chaque catégorie sont sélectionnés.

Solution utilisant les fonctions de fenêtre MySQL (MySQL 8.0.3) :

MySQL 8.0.3 introduit la prise en charge des fonctions de fenêtre standard SQL, offrant une solution plus concise et efficace :

<code class="language-sql">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;</code>

Cette requête utilise la clause PARTITION BY category_id ORDER BY date_listed DESC pour partitionner l'ensemble de résultats par catégorie et trier les éléments par ordre décroissant selon la colonne date_listed dans chaque partition. La fonction de fenêtre ROW_NUMBER() attribue ensuite des numéros de ligne consécutifs à chaque partition, permettant la sélection des quatre premiers éléments de chaque catégorie.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!