Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Requêtes MySQL Discussion :

Simplifier une requête


Sujet :

Requêtes MySQL

  1. #1
    Expert confirmé
    Simplifier une requête
    Bonjour,
    J'ai trois tables liées par des clefs étrangères à une quatrième:
    Code SQL :Sélectionner tout -Visualiser dans une fenêtre à part
    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
    CREATE TABLE t_player(
        plr_id INT PRIMARY KEY,
        plr_name VARCHAR(30) NOT NULL,
        plr_fname VARCHAR(30) NOT NULL,
    ) ENGINE=InnoDB;
     
    CREATE TABLE t_record(
        rec_id INT PRIMARY KEY AUTO_INCREMENT,
        rec_datetime DATETIME NOT NULL
    ) ENGINE=InnoDB;
     
    CREATE TABLE t_condition(
        cdn_id INT PRIMARY KEY AUTO_INCREMENT,
        cdn_tsi INT NOT NULL,
    ) ENGINE=InnoDB;
     
    CREATE TABLE t_history(
        fk_rec_id INT NOT NULL,
        fk_plr_id INT NOT NULL,
        fk_cdn_id INT NOT NULL,
     
        PRIMARY KEY (fk_plr_id, fk_rec_id),
     
        FOREIGN KEY (fk_rec_id) REFERENCES t_record(rec_id),
        FOREIGN KEY (fk_plr_id) REFERENCES t_player(plr_id),
        FOREIGN KEY (fk_cdn_id) REFERENCES t_condition(cdn_id)
    ) ENGINE=InnoDB;


    Je souhaite obtenir pour chaque joueur (t_player), la liste des valeurs TSI (t_condition) pour toutes les dates d'enregistrement (t_record): ce qui implique des valeurs null pour les valeurs TSI à certaines dates où les joueurs sont partis ou pas encore arrivés dans l'équipe.
    Le problème c'est qu'à empiler des jointures, ces valeurs null ont tendances à se faire la malle pour des raisons qui m'échappent.
    Après avoir bien bataillé, j'ai fini par aboutir à ça (qui fonctionne parfaitement):
    Code SQL :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    WITH x AS (
      SELECT plr_id, plr_name, plr_fname,
             rec_id, rec_datetime
      FROM t_player
      CROSS JOIN t_record
    ) 
    SELECT x.plr_name AS 'Name',
           x.plr_fname AS Forname,
           GROUP_CONCAT(COALESCE(c.cdn_tsi, '') ORDER BY x.rec_datetime) AS TSI
    FROM t_history h
    RIGHT JOIN x 
      ON x.plr_id = h.fk_plr_id
      AND x.rec_id = h.fk_rec_id
    LEFT JOIN t_condition c
      ON h.fk_cdn_id = c.cdn_id
    GROUP BY x.plr_id
    ORDER BY x.plr_name, x.plr_fname


    N'y a-t-il pas plus simple?
    Brachygobius xanthozonus
    Ctenobrycon Gymnocorymbus

  2. #2
    Membre averti
    L'UNION fait la force
    Je dirais, puisque tu souhaites une valeur pour chaque entraînement ; une requête UNION entre les entraînements où le joueur était présent et ceux où le joueur était absent ?
    Les problèmes sont des opportunités en vêtements de travail. Henry H. Kaiser
    Il n'est pas de problème dont une absence de solution ne finisse par venir à bout. Henri Queuille

  3. #3
    Expert éminent sénior
    J'ai eu du mal à comprendre le besoin, je crois que ça y est

    Du coup il me semble que ceci fera l'affaire :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
       select PL.plr_id         
            , PL.plr_name
            , group_concat(COALESCE(CN.cdn_tsi, '') ORDER BY RC.rec_date) AS TSI
       FROM T_record RC           
       INNER JOIN T_history HI
          ON HI.fk_rec_id = RC.rec_id
       LEFT JOIN T_player   PL    
          ON PL.plr_id=HI.fk_plr_id
       LEFT JOIN T_condition  CN
          ON CN.CDN_id=HI.fk_cdn_id
       group by PL.plr_id         
              , PL.plr_name

  4. #4
    Expert confirmé
    Merci pour vos réponses.

    Citation Envoyé par Paraffine
    une requête UNION entre les entraînements où le joueur était présent et ceux où le joueur était absent ?
    Je n'ai pas creusé cette piste, car je suis persuadé qu'on peut obtenir le résultat avec les bonnes jointures, mais c'est une idée, je ferai des essais.


    Citation Envoyé par escartefigue Voir le message
    J'ai eu du mal à comprendre le besoin, je crois que ça y est
    Désolé, ce n'est pas encore ça. Avec cette requête on obtient bien toutes les valeurs TSI concaténées pour chaque joueur, mais tout le problème est que je souhaite également figurer dans cette concaténation les fois où le joueur n'est pas là (avec des chaînes vides pour représenter les valeurs TSI manquantes).

    Prenons un exemple:
    • Jacques est présent du début à la fin et a donc une valeur TSI pour chaque date
    • Pierre est arrivé récemment et n'a pas de valeurs pour les premières dates
    • Henri était présent dés le début, mais a quitté l'équipe, il n'a donc pas de valeurs TSI pour les dernières dates


    Ta requête me donne:
    Pierre 510, 625, 804
    Jacques 1095, 1150, 1045, 1271, 1304, 1400
    Henri 975, 900, 881, 860

    Or moi ce que je veux obtenir c'est: (avec NA pour représenter les chaînes vides)
    Pierre NA, NA, NA, 510, 625, 804
    Jacques 1095, 1150, 1045, 1271, 1304, 1400
    Henri 975, 900, 881, 860, NA, NA
    Brachygobius xanthozonus
    Ctenobrycon Gymnocorymbus

  5. #5
    Membre averti
    Il faut un produit joueur * date pour tous les cas où les joueurs sont absents, du coup je me demande comment on peut s'en sortir avec les jointures ?

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    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
     
    SELECT plr.plr_name    AS 'Name',
               plr.plr_fname AS Forname,
               rec.date_time AS 'Date',
               cdn.cdn_tsi   AS TSI
     
           FROM t_history his
    INNER JOIN t_player plr    ON plr.plr_id = his.fk_plr_id
    INNER JOIN t_condition cdn ON  cdn.cdn_id = his.fk_cdn_id
    INNER JOIN t_record rec   ON  rec.rec_id = his.fk_rec_id
     
    UNION ALL
     
    SELECT plr.plr_name   AS 'Name',
               plr.plr_fname AS Forname,
               rec.date_time AS 'Date',
               'NA' AS TSI
     
           FROM  t_player plr , 
                    t_record  rec           
    WHERE NOT EXISTS (SELECT 1 FROM t_history his
                                 WHERE his.fk_rec_id = rec.rec_id
                                    AND his.fk_plr_id = plr.plr_id)
     
    ORDER BY plr.plr_name, plr.plr_fname, rec_date_time


    Mais ce n'est pas équivalent à ta requête, cette liste-là est "à plat"=> il faudrait en faire une vue
    Peut-être que tu vas garder ta formule ?
    Les problèmes sont des opportunités en vêtements de travail. Henry H. Kaiser
    Il n'est pas de problème dont une absence de solution ne finisse par venir à bout. Henri Queuille

  6. #6
    Expert confirmé
    Citation Envoyé par Paraffine
    Il faut un produit joueur * date pour tous les cas où les joueurs sont absents, du coup je me demande comment on peut s'en sortir avec les jointures ?
    Bah dans ma première version, j'ai crée une sous requête avec le produit cartésien de t_player et t_record avec CROSS JOIN.

    En partant de ton idée j'ai abouti à ([EDIT]J'avais oublier les parenthèses autour des tables du NATURAL JOIN[/EDIT])
    Code SQL :Sélectionner tout -Visualiser dans une fenêtre à part
    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
    WITH j AS (
      SELECT his.fk_plr_id    AS plr_id,
             his.fk_rec_id    AS rec_id,
             cdn.cdn_tsi      AS TSI
      FROM t_history his
      INNER JOIN t_condition cdn
              ON cdn.cdn_id = his.fk_cdn_id
     
      UNION ALL
     
      SELECT plr.plr_id       AS plr_id,
             rec.rec_id       AS rec_id,
             'NA'             AS TSI
      FROM  t_player plr, t_record rec           
      WHERE NOT EXISTS (
        SELECT 1 FROM t_history his
        WHERE his.fk_rec_id = rec.rec_id
        AND   his.fk_plr_id = plr.plr_id
      )
    )
    SELECT p.plr_name AS 'Name',
           p.plr_fname AS Forname, 
           GROUP_CONCAT(j.TSI ORDER BY r.rec_datetime) AS TSI
    FROM j
    NATURAL JOIN (t_player p, t_record r)
    GROUP BY j.plr_id
    ORDER BY p.plr_name, p.plr_fname

    pour obtenir le résultat voulu.
    Je ne saurais dire si c'est plus ou moins "simple", ma question étant en ces termes, plutôt mal posée, mais quoi qu'il en soit, je te remercie car je ne suis pas mécontent d'avoir une autre manière de faire sous la main.
    Brachygobius xanthozonus
    Ctenobrycon Gymnocorymbus

  7. #7
    Expert confirmé
    Pas besoin de CROSS JOIN d'ailleurs un simple SELECT sur deux tables fait naturellement le produit cartésien:
    Code SQL :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT p.plr_name AS 'Name',
           p.plr_fname AS Forname,
           GROUP_CONCAT(COALESCE(c.cdn_tsi, '') ORDER BY r.rec_datetime) AS TSI
    FROM t_history h
    RIGHT JOIN (SELECT plr_id, rec_id FROM t_player, t_record) AS x
      ON x.plr_id = h.fk_plr_id
      AND x.rec_id = h.fk_rec_id
    NATURAL JOIN t_record r
    LEFT JOIN t_condition c
      ON h.fk_cdn_id = c.cdn_id
    NATURAL JOIN t_player p
    GROUP BY p.plr_id
    ORDER BY p.plr_name, p.plr_fname


    Citation Envoyé par Paraffine
    il faudrait en faire une vue
    Quelque soit la solution retenue (au passage toute suggestion est la bienvenue), ça me semble être une bonne idée, car peu de mises à jour.
    Brachygobius xanthozonus
    Ctenobrycon Gymnocorymbus

  8. #8
    Expert éminent sénior
    Citation Envoyé par CosmoKnacki Voir le message
    Pas besoin de CROSS JOIN d'ailleurs un simple SELECT sur deux tables fait naturellement le produit cartésien:
    oui, ces 3 formulations produisent le même résultat

    FROM T1 CROSS JOIN T2.

    FROM T1 INNER JOIN T2 ON 1=1.

    FROM TI, T2.

    Mais l'absence de critères de jointure est moins lisible et peut passer pour un oubli, un produit cartésien explicite a le mérite d'être clair

  9. #9
    Expert confirmé
    Ayé

    Code SQL :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT p.plr_name AS 'Name',
           p.plr_fname AS Forname,
           GROUP_CONCAT(COALESCE(c.cdn_tsi, '') ORDER BY r.rec_datetime) AS TSI
    FROM (t_player p, t_record r)
    LEFT JOIN (t_history h, t_condition c)
      ON  h.fk_plr_id = p.plr_id 
      AND h.fk_rec_id = r.rec_id
      AND h.fk_cdn_id = c.cdn_id
    GROUP BY p.plr_id
    ORDER BY p.plr_name, p.plr_fname

    ou dans sa version plus explicite:

    Code SQL :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    SELECT p.plr_name AS 'Name',
           p.plr_fname AS Forname,
           GROUP_CONCAT(COALESCE(c.cdn_tsi, '') ORDER BY r.rec_datetime) AS TSI
    FROM t_player p
    CROSS JOIN t_record r
    LEFT JOIN t_history h
      ON  h.fk_plr_id = p.plr_id 
      AND h.fk_rec_id = r.rec_id
    LEFT JOIN t_condition c
      ON  h.fk_cdn_id = c.cdn_id
    GROUP BY p.plr_id
    ORDER BY p.plr_name, p.plr_fname
    Brachygobius xanthozonus
    Ctenobrycon Gymnocorymbus

###raw>template_hook.ano_emploi###