Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 9 sur 9
  1. #1
    ced
    ced est déconnecté
    Rédacteur/Modérateur

    Avatar de ced
    Homme Profil pro Cédric Duprez
    Gestion de bases de données techniques
    Inscrit en
    avril 2002
    Messages
    5 125
    Détails du profil
    Informations personnelles :
    Nom : Homme Cédric Duprez
    Âge : 39
    Localisation : France, Loiret (Centre)

    Informations professionnelles :
    Activité : Gestion de bases de données techniques
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : avril 2002
    Messages : 5 125
    Points : 17 653
    Points
    17 653

    Par défaut Obtenir les n premiers éléments de chaque catégorie

    Bonjour,

    Je vous propose un nouvel élément à utiliser : Obtenir les n premiers éléments de chaque catégorie

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

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    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 :

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    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 :

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    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).

    Qu'en pensez-vous ?
    Rédacteur / Modérateur SGBD et R
    Mes tutoriels et la FAQ MySQL

    ----------------------------------------------------
    Pensez aux balises code et au tag
    Une réponse vous a plu ? N'hésitez pas à y mettre un
    Je ne réponds pas aux questions techniques par message privé, les forums sont là pour ça

  2. #2
    Membre Expert
    Avatar de gene69
    Profil pro
    Inscrit en
    janvier 2006
    Messages
    1 633
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : janvier 2006
    Messages : 1 633
    Points : 2 122
    Points
    2 122

    Par défaut

    ç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?
    PHP fait nativement la validation d'adresse électronique .
    Celui qui a inventé mysql_connect(...) or die() est déjà mort plusieurs fois.
    Soyez moderne: mysqli_connect() or throw Exception(mysqli_connect_error());

    PHP: un problème ? décrivez le avec ceci.

    Utilisez le bouton résolu!

  3. #3
    Expert Confirmé Avatar de pacmann
    Homme Profil pro Pacman Pacman
    Consulté Oracle
    Inscrit en
    juin 2004
    Messages
    1 626
    Détails du profil
    Informations personnelles :
    Nom : Homme Pacman Pacman
    Âge : 33
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Consulté Oracle
    Secteur : Distribution

    Informations forums :
    Inscription : juin 2004
    Messages : 1 626
    Points : 2 822
    Points
    2 822

    Par défaut

    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)

    (c'est ma photo)
    Paku, Paku !
    Pour les jeunes incultes : non, je ne suis pas un pokémon...

    Le pacblog : http://pacmann.over-blog.com/

  4. #4
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 895
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 895
    Points : 25 031
    Points
    25 031

    Par défaut

    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 :
    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
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  5. #5
    Expert Confirmé Avatar de pacmann
    Homme Profil pro Pacman Pacman
    Consulté Oracle
    Inscrit en
    juin 2004
    Messages
    1 626
    Détails du profil
    Informations personnelles :
    Nom : Homme Pacman Pacman
    Âge : 33
    Localisation : France, Bas Rhin (Alsace)

    Informations professionnelles :
    Activité : Consulté Oracle
    Secteur : Distribution

    Informations forums :
    Inscription : juin 2004
    Messages : 1 626
    Points : 2 822
    Points
    2 822

    Par défaut

    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 :
    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)

    (c'est ma photo)
    Paku, Paku !
    Pour les jeunes incultes : non, je ne suis pas un pokémon...

    Le pacblog : http://pacmann.over-blog.com/

  6. #6
    Membre Expert

    Inscrit en
    juillet 2006
    Messages
    1 421
    Détails du profil
    Informations forums :
    Inscription : juillet 2006
    Messages : 1 421
    Points : 1 086
    Points
    1 086

    Par défaut

    Ç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 :
    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.

  7. #7
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro Frédéric BROUARD
    Expert SGBDR & SQL
    Inscrit en
    mai 2002
    Messages
    13 640
    Détails du profil
    Informations personnelles :
    Nom : Homme Frédéric BROUARD
    Localisation : France

    Informations professionnelles :
    Activité : Expert SGBDR & SQL
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 13 640
    Points : 30 216
    Points
    30 216

    Par défaut

    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro
    http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  8. #8
    Membre Expert

    Inscrit en
    juillet 2006
    Messages
    1 421
    Détails du profil
    Informations forums :
    Inscription : juillet 2006
    Messages : 1 421
    Points : 1 086
    Points
    1 086

    Par défaut

    En MySQL, vous pouvez définir une variable entière à 0 et renvoyé sa valeur tout en l'incrémentant dans un select ordonné (order by...).
    Je ne me souviens pas de la syntax exact mais c'est une chose à connaître dans ces cas là.

    Note : Quel déterrement !
    Most Valued Pas mvp

  9. #9
    Membre Expert
    Avatar de alassanediakite
    Homme Profil pro Alassane Diakité
    Recherche, formation, développement
    Inscrit en
    août 2006
    Messages
    1 131
    Détails du profil
    Informations personnelles :
    Nom : Homme Alassane Diakité
    Âge : 37
    Localisation : Mali

    Informations professionnelles :
    Activité : Recherche, formation, développement

    Informations forums :
    Inscription : août 2006
    Messages : 1 131
    Points : 2 046
    Points
    2 046

    Par défaut

    Salut
    Un exemple avec variable initialisée à zéro
    Code :
    1
    2
    SET @rank=0;
    SELECT @rank:=@rank+1 as rank, ID, Name FROM city;
    @+
    Le monde est trop bien programmé pour être l’œuvre du hasard…
    Mon produit pour la gestion d'école: www.logicoles.com

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •