IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Téléchargé 3 fois
Vote des utilisateurs
0 
0 
Détails
Licence : Non renseignée
Mise en ligne le 13 février 2011
Langue : Français
Référencé dans
Navigation

Obtenir les n premiers éléments de chaque catégorie

Supposons qu'une table ELEMENT et une table CATEGORIE sont composées comme suit :

CREATE TABLE `ma_base`.`CATEGORIE` (
`id_categorie` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`nom_categorie` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id_categorie`)
)
ENGINE = InnoDB;

CREATE TABLE `ma_base`.`ELEMENT` (
`id_element` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`nom_element` VARCHAR(45) NOT NULL,
`id_categorie` INTEGER UNSIGNED NOT NULL,
PRIMARY KEY (`id_element`),
CONSTRAINT `FK_ELT_CAT` FOREIGN KEY `FK_ELT_CAT` (`id_categorie`)
REFERENCES `categorie` (`id_categorie`)
ON DELETE CASCADE
ON UPDATE CASCADE
)
ENGINE = InnoDB;


Le but de la requête est de ramener les n premiers éléments de chaque catégorie par ordre d'identifiant. Pour cela, on fait ce qu'on appelle une division relationnelle, comme suit :

SELECT
e.id_element, e.nom_element, c.id_categorie, c.nom_categorie
FROM
ELEMENT e
INNER JOIN
CATEGORIE c ON e.id_categorie = c.id_categorie
WHERE (
SELECT COUNT(*)
FROM ELEMENT e1
WHERE e1.id_categorie = e.id_categorie
AND e1.id_element < e.id_element
) < n


Il suffit de remplacer n par la valeur souhaitée.

Pour obtenir les n derniers éléments de chaque catégorie, il suffit d'inverser le sens de l'inégalité dans la sous-requête :

SELECT
e.id_element, e.nom_element, c.id_categorie, c.nom_categorie
FROM
ELEMENT e
INNER JOIN
CATEGORIE c ON e.id_categorie = c.id_categorie
WHERE (
SELECT COUNT(*)
FROM ELEMENT e1
WHERE e1.id_categorie = e.id_categorie
AND e1.id_element > e.id_element
) < n

Attention, cette requête ne fonctionne qu'avec une version de MySQL supportant les sous-requêtes (version 4.1 ou postérieure).
Avatar de gene69
Membre émérite https://www.developpez.com
Le 14/02/2011 à 10:49
ça à l'air sympa. ya l'équivalent d'une base scott/tiger pour mysql? je veux dire la même chose avec des données.

point de vue calculs, c'est gourmand?
Avatar de pacmann
Membre émérite https://www.developpez.com
Le 08/12/2011 à 14:09
Salut !

Je trouve ça juste un peu dommage que l'exemple donne un tri sur une PK technique

Genre j'aurais bien vu par exemple une date de création à la place, et combiner l'ordre date_creation + increment (pour départager les ex-aequos et garantir qu'on en sorte au plus n)
Avatar de CinePhil
Modérateur https://www.developpez.com
Le 08/12/2011 à 14:24
Je me suis posé la même question tout récemment car j'ai eu à sortir un top 5 selon deux critères, l'un classé en ASC et l'autre en DESC.

Comme il n'y a pas beaucoup de catégories, je suis passé par des requêtes UNION et LIMIT 5. Un truc du genre :
Code : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT tmp.les_colonnes
FROM
(
	(
		SELECT la_categorie, les_colonnes
		FROM la_table
		WHERE la_categorie = 1
		ORDER BY un_critere ASC, un_autre_critere DESC
		LIMIT 5
	)
	UNION ALL
	(
		SELECT la_categorie, les_colonnes
		FROM la_table
		WHERE la_categorie = 2
		ORDER BY un_critere ASC, un_autre_critere DESC
		LIMIT 5
	)
	UNION ALL
-- etc
) tmp
ORDER BY la_categorie, un_critere ASC, un_autre_critere DESC
Avatar de pacmann
Membre émérite https://www.developpez.com
Le 08/12/2011 à 14:29
Quand tu as deux (ou plus) critères, il faut réaliser les combinaisons en priorisant.

Dans ton cas, la condition du count(*) de ced serait un truc du genre :
Code : Sélectionner tout
1
2
WHERE a.un_critere < b.un_critèreASC OR (a.un_critere = b.un_critere AND a.un_autre_critere > b.un_autre_critere)
Avatar de Sergejack
Membre éprouvé https://www.developpez.com
Le 16/12/2011 à 14:10
Ça ressemble (en même temps il n'y a pas des milliers de possibilité en MySQL) à un query que j'ai écrit récemment.
Il y a une subtilité supplémentaire dans mon query, c'est une optimisation prenant tout son sens lorsque des critères de recherche impliquent des jointures.

En effet, si des lignes doivent être éliminées du résultats, elles doivent l'être autant dans le sous query qui calcul l'ordre de la ligne (dépendante d'un tri) que dans le query principal.
Pour ne pas répéter deux fois des jointures qui ne serviraient qu'au filtrage, je procède donc ainsi :

Code : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SELECT
	H.id
	, H.stockID
	, H.value
	, H.date
	, H.userID
	, U.name AS userName
FROM #__mystock_history AS H
LEFT JOIN #__users AS U ON (U.id = H.userID)
WHERE H.stockID IN (%1$s)
AND (
	SELECT
		 COUNT(*) * SUM(CASE WHEN H.id = H2.id THEN 1 ELSE 0 END)
	FROM #__mystock_history AS H2
	LEFT JOIN #__mystock_cancel AS C1 ON (C1.canceledID = H2.id)
	LEFT JOIN #__mystock_cancel AS C2 ON (C2.correctionID = H2.id)
	WHERE H2.stockID = H.stockID
	AND C1.canceledID IS NULL
	AND C2.correctionID IS NULL
	AND H2.Date >= H.Date
	AND (
		H2.Date > H.Date
		OR
		H2.id >= H.id
	)
) BETWEEN 1 AND %2$d
ORDER BY H.stockID ASC, H.date DESC
%1$s = une liste d'identifiant séparés par des virgules (ex "1,2,3,4"
%2$d = Le nombre maximale de lignes à obtenir par "groupe"

L'idée c'est d'obtenir dans le sous query une formule qui puisse m'inidquer deux choses :
1) l'ordre de la ligne
2) si la ligne convient au filtre

Le sous query renverra 0 si la ligne ne convient pas au filtre et l'ordre de la ligne sinon.
Avatar de SQLpro
Rédacteur https://www.developpez.com
Le 21/03/2013 à 15:21
Elle n'est valable QUE dans le cas ou vous utilisez une clef primaire comme comparaison d’inégalité. Si vous utilisez une colonne quelconque, cela peut conduire à des résultats faux.

Dans ce ac, seule la fonction ROW_NUMBER() ou RANK(), implémentée dans tous les SGBDR sauf MySQL sera la seule solution.

A +
Avatar de Sergejack
Membre éprouvé https://www.developpez.com
Le 21/03/2013 à 17:08
En MySQL, vous pouvez définir une variable entière à 0 et renvoyer sa valeur tout en l'incrémentant dans un select ordonné (order by...).
Je ne me souviens pas de la syntaxe exacte, mais c'est une chose à connaître dans ces cas-là.

Note : Quel déterrement !
Avatar de alassanediakite
Membre expert https://www.developpez.com
Le 18/05/2013 à 15:21
Salut
Un exemple avec variable initialisée à zéro
Code : Sélectionner tout
1
2
SET @rank=0;
SELECT @rank:=@rank+1 as rank, ID, Name FROM city;
@+
Developpez.com décline toute responsabilité quant à l'utilisation des différents éléments téléchargés.