Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 6 sur 6
  1. #1
    Invité de passage
    Femme Profil pro
    Étudiant
    Inscrit en
    décembre 2012
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Femme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : décembre 2012
    Messages : 5
    Points : 0
    Points
    0

    Par défaut SQL 'group by'

    Bonjour

    J'ai une base de données permettant la gestion entre les medecins et leurs patients et contenant les tables suivantes :
    PATIENT (numAs, nomp, prenomp, sexe, dateNaiss, matriculem#)
    MEDECIN (matricule, nomm, prenomm, specialite, ville, adr)
    CONSULTE (matricule#, numAs#, date, diagnostic,numOrd#)

    indication: matriculem est une clè étrangère qui a pour référence medecin(matricule)
    je dois écrire la requête SQL qui permet de : "Afficher le nom et le prénom du ou des médecins qui sont médecins traitants du plus grand
    nombre de patients "

    quand j'écris :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    SELECT  nomm,prenomm,count(numas) 
    FROM patient,medecin 
    GROUP BY matriculem
    HAVING 
    (
    	matriculem = matricule 
    	AND count(numas) =
    	(
    		SELECT max(champ) 
    		FROM 
    		(
    			SELECT count(numas) AS champ 
    			FROM patient 
    			GROUP BY matriculem
    		) y
    	)
    );
    --> cela ne fonctionne pas

    mais quand j'écris (en suivant les messages de postgres)
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    SELECT  nomm,prem,count(numas) 
    FROM patient,medecin 
    GROUP BY matriculem, medecin.nomm, medecin.prenomm, medecin.matricule
    HAVING
    (
    	matriculem = matricule 
    	AND count(numas) =
    	(
    		SELECT max(champ) 
    		FROM 
    		(
    			SELECT count(numas) AS champ 
    			FROM patient 
    			GROUP BY matriculem
    		) y
    	)
    )
    ;

    --> cela fonctionne très bien

    cependant je ne vois pas pourquoi j'ai à ajouter au 'group by' le nomm le prenomm et la matricule de la table medecin.
    quelqu'un pourrait m'expliquer cela ? et aussi,j'aimerais bien savoir s'il y a une façon plus simple(plus claire) pour écrire cette requête.

  2. #2
    Expert Confirmé Sénior
    Inscrit en
    août 2008
    Messages
    2 185
    Détails du profil
    Informations forums :
    Inscription : août 2008
    Messages : 2 185
    Points : 4 022
    Points
    4 022

    Par défaut

    cependant je ne vois pas pourquoi j'ai à ajouter au 'group by' le nomm le prenomm
    Toutes colonnes sélectionnées et non-agrégées font l'objet d'un regroupement et doivent donc figurer dans la clause GROUP BY.
    et aussi,j'aimerais bien savoir s'il y a une façon plus simple(plus claire) pour écrire cette requête.
    Déjà il va falloir réviser les jointures, ensuite on peut faire comme ça :
    Code :
    1
    2
    3
    4
    5
    6
    7
    select m.nomm, m.prenonm, count(p.numas) as nb_patient
      from medecin m
      join patient p on p.matriculem = m.matricule
     group by m.nomm, m.prenonm
    having count(p.numas) >= all (select count(p2.numas)
                                    from patient p2
                                   group by p2.matriculem)

  3. #3
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 890
    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 890
    Points : 25 016
    Points
    25 016

    Par défaut

    Il ya aurait beaucoup de choses à revoir dans la BDD et dans ces requêtes !

    1)
    PATIENT (numAs, nomp, prenomp, sexe, dateNaiss, matriculem#)
    MEDECIN (matricule, nomm, prenomm, specialite, ville, adr)
    CONSULTE (matricule#, numAs#, date, diagnostic,numOrd#)
    Je suppose que "numAS" signifie "numéro d'assuré social" ?
    Ce numéro, comme le matricule du médecin, surtout si ce dernier n'est pas entièrement numériques, sont de mauvaises clés.

    La ville devrait être externalisée dans une table des villes afin d'éviter d'avoir plusieurs fois la même ville orthographiée de différentes manières.

    Idem pour la spécialité du médecin.

    2)
    je ne vois pas pourquoi j'ai à ajouter au 'group by' le nomm le prenomm et la matricule de la table medecin.
    Tout simplement parce que toutes les colonnes du SELECT ne figurant pas dans une fonction de groupage doivent figurer dans le GROUP BY sous peine de récupérer pour les colonnes absentes des résultats aléatoires.

    3) Les jointures s'écrivent depuis 20 ans avec l'opérateur JOIN. Il serait temps de s'y mettre !
    Et même écrite à l'ancienne, la condition de jointure ne se met pas dans le HAVING !

    4)
    "Afficher le nom et le prénom du ou des médecins qui sont médecins traitants du plus grand
    nombre de patients "

    j'aimerais bien savoir s'il y a une façon plus simple(plus claire) pour écrire cette requête.
    Procédons par étapes...

    A) Nombre de patients par médecin :
    Code :
    1
    2
    3
    4
    SELECT matriculem,
    	COUNT(*) AS nb_patients
    FROM CONSULTE
    GROUP BY matriculem
    B) Plus grand nombre de patients pour un médecin :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT MAX(t1.nb_patients) AS max_nb_patients
    FROM
    (
    	SELECT matriculem,
    		COUNT(*) AS nb_patients
    	FROM CONSULTE
    	GROUP BY matriculem
    ) t1
    C) Médecin(s) ayant le lus grand nombre de patients :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    SELECT matriculem
    FROM CONSULTE
    HAVING COUNT(*) =
    (
    	SELECT MAX(t1.nb_patients)
    	FROM
    	(
    		SELECT matriculem,
    			COUNT(*) AS nb_patients
    		FROM CONSULTE
    		GROUP BY matriculem
    	) t1
    )
    GROUP BY matriculem
    D) Nom et prénom de ce ou ces médecins :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    SELECT m.nomm, m.prenomm
    FROM MEDECIN m
    INNER JOIN CONSULTE c ON c.matriculem = m.matricule
    HAVING COUNT(*) =
    (
    	SELECT MAX(t1.nb_patients)
    	FROM
    	(
    		SELECT matriculem,
    			COUNT(*) AS nb_patients
    		FROM CONSULTE
    		GROUP BY matriculem
    	) t1
    )
    GROUP BY m.nomm, m.prenomm
    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 !

  4. #4
    Invité de passage
    Femme Profil pro
    Étudiant
    Inscrit en
    décembre 2012
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Femme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : décembre 2012
    Messages : 5
    Points : 0
    Points
    0

    Par défaut

    Merci pour votre réponse très rapide


    Citation Envoyé par skuatamad Voir le message
    Toutes colonnes sélectionnées et non-agrégées font l'objet d'un regroupement et doivent donc figurer dans la clause GROUP BY.

    Déjà il va falloir réviser les jointures,

    1) Il paraît que j'avais encore de graves lacunes mais avec vos aides et un peu de lecture en suivant les liens indiqués, j'ai commencé à y remédier...

    ensuite on peut faire comme ça :
    Code :
    1
    2
    3
    4
    5
    6
    7
    select m.nomm, m.prenonm, count(p.numas) as nb_patient
      from medecin m
      join patient p on p.matriculem = m.matricule
     group by m.nomm, m.prenonm
    having count(p.numas) >= all (select count(p2.numas)
                                    from patient p2
                                   group by p2.matriculem)

    2) Ce code me paraît vraiment très net,simple mais aussi court
    Merci encore

  5. #5
    Invité de passage
    Femme Profil pro
    Étudiant
    Inscrit en
    décembre 2012
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Femme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : décembre 2012
    Messages : 5
    Points : 0
    Points
    0

    Par défaut

    Merci pour vos réponses rapides et fructueuses

    Citation Envoyé par CinePhil Voir le message
    Il ya aurait beaucoup de choses à revoir dans la BDD et dans ces requêtes !

    1)

    Je suppose que "numAS" signifie "numéro d'assuré social" ?
    Ce numéro, comme le matricule du médecin, surtout si ce dernier n'est pas entièrement numériques, sont de mauvaises clés.

    La ville devrait être externalisée dans une table des villes afin d'éviter d'avoir plusieurs fois la même ville orthographiée de différentes manières.

    Idem pour la spécialité du médecin.
    1) Ce sont les données que contient l'exercice(assez limité), ce n'est donc pas moi qui ai créé cette BD mais j'ai oublié d'indiquer les clés primaires de chaque table, je m'excuse et je rectifie alors:


    PATIENT (numAs, nomp, prenomp, sexe, dateNaiss, matriculem#)
    MEDECIN (matricule, nomm, prenomm, specialite, ville, adr)
    CONSULTE (matricule#, numAs#, date, diagnostic,numOrd#)

    J'ai quand même saisi ce que vous voulez dire et ce qu'il manque à l'exercice

    2)

    Tout simplement parce que toutes les colonnes du SELECT ne figurant pas dans une fonction de groupage doivent figurer dans le GROUP BY sous peine de récupérer pour les colonnes absentes des résultats aléatoires.

    2) Il paraît que j'avais encore de graves lacunes mais avec vos aides et un peu de lecture, j'ai commencé à y remédier...

    3) Les jointures s'écrivent depuis 20 ans avec l'opérateur JOIN. Il serait temps de s'y mettre !
    Et même écrite à l'ancienne, la condition de jointure ne se met pas dans le HAVING !
    3) Je suis assez trop ancienne donc ! ^^ Les jointures, je ne sais pas pourquoi je les ai 'toujours' évitées...

    4)


    Procédons par étapes...

    A) Nombre de patients par médecin :
    Code :
    1
    2
    3
    4
    SELECT matriculem,
    	COUNT(*) AS nb_patients
    FROM CONSULTE
    GROUP BY matriculem
    B) Plus grand nombre de patients pour un médecin :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT MAX(t1.nb_patients) AS max_nb_patients
    FROM
    (
    	SELECT matriculem,
    		COUNT(*) AS nb_patients
    	FROM CONSULTE
    	GROUP BY matriculem
    ) t1
    C) Médecin(s) ayant le lus grand nombre de patients :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    SELECT matriculem
    FROM CONSULTE
    HAVING COUNT(*) =
    (
    	SELECT MAX(t1.nb_patients)
    	FROM
    	(
    		SELECT matriculem,
    			COUNT(*) AS nb_patients
    		FROM CONSULTE
    		GROUP BY matriculem
    	) t1
    )
    GROUP BY matriculem
    D) Nom et prénom de ce ou ces médecins :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    SELECT m.nomm, m.prenomm
    FROM MEDECIN m
    INNER JOIN CONSULTE c ON c.matriculem = m.matricule
    HAVING COUNT(*) =
    (
    	SELECT MAX(t1.nb_patients)
    	FROM
    	(
    		SELECT matriculem,
    			COUNT(*) AS nb_patients
    		FROM CONSULTE
    		GROUP BY matriculem
    	) t1
    )
    GROUP BY m.nomm, m.prenomm
    4) en suivant votre démarche, je pourrais y voir plus claires : c'est bien organisé dans ma tête là
    toutefois, ici on a matricule(table medecin et consulte) et matriculem(table patient), il y a donc une confusion d'utilisation de matricule et matriculem dans votre code.
    Encore faute de clarté de l'exercice, à la fin vous avez utilisé la table CONSULTE pour la jointure au lieu de la table PATIENT alors que le médecin traitant est repéré par son "matriculem" dans la table patient, et le consultant par son "matricule" dans la table CONSULTE, et nous cherchons le médecin traitant.
    D'autre part, le HAVING doit en principe être après le GROUP BY.

    Alors, le beau code final:

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT nomm,prenomm from medecin m 
    		INNER JOIN patient p on matricule=matriculem
    		GROUP BY nomm,prenomm
    		HAVING count(*)=
                                     ( SELECT MAX(nb_patients)
    					FROM
    					(
    						SELECT matricule,
    						COUNT(*) AS nb_patients
    						FROM CONSULTE
    						GROUP BY matricule
    						) t
    						);

  6. #6
    Invité de passage
    Femme Profil pro
    Étudiant
    Inscrit en
    décembre 2012
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Femme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : décembre 2012
    Messages : 5
    Points : 0
    Points
    0

    Par défaut 2ème question

    Pour voir si j'ai bien appris la bonne démarche à suivre pour écrire mes requêtes SQL,
    et afin d'afficher les noms de tous les patients qui ont visité tous les médecins radiologues de leurs régions,
    pourriez-vous me dire si ce code(il donne bien le résultat désiré) est bon et clair ?
    y a t-il plus simple ?
    J'ai du ajouter la colonne "villep" à la table PATIENT.

    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    select nomp,prenomp,numas from medecin m
    		join patient p on p.villep=m.ville
    		where specialite='radiologue' group by nomp,prenomp,numas
    		having (count(*),p.numas) in (select distinct count(*),p.numas 
    					from (select m.matricule,ville,numas from consulte c 
    									join medecin m on c.matricule=m.matricule
    									where specialite='radiologue' ) y
    									join patient p on p.numas=y.numas and p.villep=y.ville
    									group by p.numas );

+ Répondre à la discussion
Cette discussion est résolue.

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
  •