Requete SQL avec comptage

**mpereg** · 17/03/2008, 20h07

Bonjour,

Voici l’architecture de la base :

Je voudrais faire une requête qui pour un ProfilID donné dresse la liste de tous les collaborateurs en calculant :

1. Le nombre de compétences identiques qui sont rencontrés à la fois dans le profil et chez le collaborateur (si aucune 0)

2. Le nombre total des compétences d'un profil

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
CollaborateurID   Nom  Prenom      Nb Compétences / Total Compétences du profil
---------------+------+---------+----------------------------------------------

Merci

**Alain Defrance** · 17/03/2008, 21h39

1/

Le nombre de compétences identiques qui sont rencontrés à la fois dans le profil et chez le collaborateur (si aucune 0)

Tu peut détailler un peut plus ? tu voudrais avoir le nombre de compétences différentes présente chez un collaborateur, je ne comprend pas trop le sens de la question en rapport avec ce MCD.

2/

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT COUNT(*) AS nbCompetences
FROM Profils
INNER JOIN CompetencesProfils
ON Profils.ProfilID = Competences.ProfilID;

Ton MCD manque de cardinalité, ce qui implique que c'est difficile de voir les jointures a faire ni voir le sens de l'information.
PS : La prochaine fois apporte déjà une solution perso, même si ça marche pas, ça aidera a t'aider, et tu aura plus de réponses

**mpereg** · 17/03/2008, 23h45

Merci,

1/ Je voudrais :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
1. Je me donne un profil
2. Pour ce profil je regarde les compétences qui sont liées au profil
    2.i Je regarde les collaborateurs qui ont cette compétence
    2.j Si le collaborateur a cette compétence je l'incrémente de 1
         Je passe à la compétence suiavnte

**Alain Defrance** · 18/03/2008, 00h03

hum donc si je comprend bien tu voudrais une résultat du type

mes compétences :
comp 1 : 3 collaborateur la possède
comp 2 : 2
comp 6 : 1
comp 14 : 9

c'est bien cela ?

**Sekigawa** · 18/03/2008, 14h08

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT CollaborateurID, nom, prenom, count(competencecollaborateurID) as nbcompcollabo, count(CompetencesProfilsID) as TotCompProf
FROM Collaborateurs as C, CompetencesCollaborateurs as CC, Competences as Comp, CompetencesProfils as CP, Profils as P
Where C.CollaborateurID = CC.CollaborateurID
AND Comp.CompetencesID=CC.CompetencesCollaborateursID
AND Comp.CompetenceID=CP.COmpetenceID
AND CP.ProfilID=CP.ProfilID
GROUP BY CollaborateurID, nom, prenom;

Alors m'en veut pas pour les jointure je fais comme j'ai appris lol
A tester

**Alain Defrance** · 18/03/2008, 18h29

Personne t'en voudras

Je préconise plutôt l'utilisation des join en terme d'optimisation et de sens, mais après, c'est pas la fin de monde non plus

**mpereg** · 18/03/2008, 19h49

Merci à tous,

J'ai finalement réussi à faire une requête (il m'a fallu un après-midi). Mais cette requête n'est pas optimisée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT Collaborateurs.CollaborateurID, Collaborateurs.Nom,
       COUNT(CompetencesCollaborateurs.CompetencesCollaborateursID) AS NbCompetences,
       (SELECT COUNT (*) FROM CompetencesProfils WHERE ProfilID = 24) AS NbTotCompetences
FROM  Collaborateurs
LEFT OUTER JOIN CompetencesCollaborateurs
       ON (CompetencesCollaborateurs.CollaborateurID = Collaborateurs.CollaborateurID)
WHERE CompetencesCollaborateurs.CompetenceID IN (SELECT CompetencesProfils.CompetenceID
                                                 FROM CompetencesProfils
                                                 WHERE CompetencesProfils.ProfilID = 24)
      OR CompetencesCollaborateurs.CompetenceID IS NULL
GROUP BY Collaborateurs.CollaborateurID, Collaborateurs.Nom

Il y a peut être quelque chose de mieux à faire au niveau des jointures ? Des sous requetes ?

**say** · 19/03/2008, 09h45

je pense que tu dois pouvoir essayer ça : (le IN est gourmand)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SELECT      Collaborateurs.CollaborateurID, Collaborateurs.Nom, COUNT(CompetencesCollaborateurs.CompetencesCollaborateursID) AS NbCompetences, 
      (SELECT COUNT (*) FROM CompetencesProfils WHERE ProfilID = 24) AS NbTotCompetences  
FROM  Collaborateurs
 
LEFT OUTER JOIN ( CompetencesCollaborateurs 
INNER JOIN CompetencesProfils 
ON CompetencesCollaborateurs.CompetenceID = CompetencesProfils.CompetenceID and CompetencesProfils.CompetenceID = 24)
)
 ON (CompetencesCollaborateurs.CollaborateurID = Collaborateurs.CollaborateurID)
  
GROUP BY Collaborateurs.CollaborateurID, Collaborateurs.Nom

[dans ton post, merci d'éditer pour supprimer les sauts de lignes, ça gène la lisibilité.]

**say** · 19/03/2008, 09h47

Envoyé par kazou

Personne t'en voudras

Je préconise plutôt l'utilisation des join en terme d'optimisation et de sens, mais après, c'est pas la fin de monde non plus

pas la fin du monde, mais la fin de la lisibilité

**Sekigawa** · 19/03/2008, 10h00

Envoyé par say

pas la fin du monde, mais la fin de la lisibilité

Bah là faut se plaindre à mes profs qui m'ont appris comme ça et qui banisse les join du moins pour l'examen

**say** · 19/03/2008, 10h52

mouais...dans un soucis pédagogique...mais bon...vous l'apprendre c'est qd même incontournable.

**vmolines** · 19/03/2008, 10h58

Envoyé par Sekigawa

Bah là faut se plaindre à mes profs qui m'ont appris comme ça et qui banisse les join du moins pour l'examen

Ben si tu veux te faire plaisir, tu leur écris des requêtes avec du JOIN bien indenté, qui donne le même résultat, est plus conforme, est bien plus lisible. Je ne pense pas qu'ils puissent te le reprocher et te forcer à écrire du WHERE surtout si tu es capable de faire les deux.

**Alain Defrance** · 19/03/2008, 11h07

Bah là faut se plaindre à mes profs qui m'ont appris comme ça et qui banisse les join du moins pour l'examen

Le miens aussi ne t'inquette pas, mais ca ne m'empeche pas de garder mes convictions (qui sont de plus fondés), sur l'utilisation des where a bannir pour les jointures.

Et puis ca serait quand meme fort qu'on nous pénalise pour ca

**Sekigawa** · 19/03/2008, 14h35

Envoyé par kazou

Le miens aussi ne t'inquette pas, mais ca ne m'empeche pas de garder mes convictions (qui sont de plus fondés), sur l'utilisation des where a bannir pour les jointures.

Et puis ca serait quand meme fort qu'on nous pénalise pour ca

C'est clair je pense que c'est des mytho... il faut coder la méthode dont on est le plus à l'aise ^^

**Alain Defrance** · 19/03/2008, 16h11

A vrais dire on est a l'aise très souvent avec la première technique que l'on découvre, et on enseigne très souvent les jointures par produit cartésien (le WHERE) et non pas par les join.
C'est souvent acquis plus facilement par les élèves et accepté par les examen alors on peut comprendre qu'il déconseillent l'utilisation des join.donc il faut pas taper sur les profs il faut les comprendre.

Nous dérivons un peu, nous égarons pas

**Sekigawa** · 19/03/2008, 16h18

Oui on divague mais bon j'ai pas encore accés à la Taverne...

Mais bon sujet clos !!!

**mpereg** · 19/03/2008, 20h20

Envoyé par say

je pense que tu dois pouvoir essayer ça : (le IN est gourmand)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SELECT Collaborateurs.CollaborateurID, Collaborateurs.Nom,
        COUNT(CompetencesCollaborateurs.CompetencesCollaborateursID) AS NbCompetences, 
       (SELECT COUNT (*) FROM CompetencesProfils WHERE ProfilID = 24) AS NbTotCompetences  
FROM  Collaborateurs
LEFT OUTER JOIN (CompetencesCollaborateurs 
                 INNER JOIN CompetencesProfils 
                            ON CompetencesCollaborateurs.CompetenceID = CompetencesProfils.CompetenceID
                            AND CompetencesProfils.CompetenceID = 24)
                 ON (CompetencesCollaborateurs.CollaborateurID = Collaborateurs.CollaborateurID)
GROUP BY Collaborateurs.CollaborateurID, Collaborateurs.Nom

[dans ton post, merci d'éditer pour supprimer les sauts de lignes, ça gène la lisibilité.]

J'ai testé sous Access qui me dit "Expression de Jointure Non Supportée". Je me suis peut-être trompé dans une parenthèse ?

**mpereg** · 19/03/2008, 20h55

Autre chose aussi j'ai essayé d'ajouter une ligne dans ma requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT Collaborateurs.CollaborateurID, Collaborateurs.Nom,
       COUNT(CompetencesCollaborateurs.CompetencesCollaborateursID) AS NbCompetences,
       (SELECT COUNT (*) FROM CompetencesProfils WHERE ProfilID = 24) AS NbTotCompetences,
       AVG(CompetencesProfils.Evaluation) AS MoyCompetences
FROM  Collaborateurs
LEFT OUTER JOIN CompetencesCollaborateurs
       ON (CompetencesCollaborateurs.CollaborateurID = Collaborateurs.CollaborateurID)
WHERE CompetencesCollaborateurs.CompetenceID IN (SELECT CompetencesProfils.CompetenceID
                                                 FROM CompetencesProfils
                                                 WHERE CompetencesProfils.ProfilID = 24)
      OR CompetencesCollaborateurs.CompetenceID IS NULL
GROUP BY Collaborateurs.CollaborateurID, Collaborateurs.Nom

Mais il me dit que la paramètre n'a pas de valeur par défaut !

**say** · 19/03/2008, 22h04

Envoyé par mpereg

J'ai testé sous Access qui me dit "Expression de Jointure Non Supportée". Je me suis peut-être trompé dans une parenthèse ?

peut-etre le OUTER, j'avais pas fait gaffe que tu étais sous Access.

**Antoun** · 20/03/2008, 00h23

Envoyé par kazou

A vrais dire on est a l'aise très souvent avec la première technique que l'on découvre, et on enseigne très souvent les jointures par produit cartésien (le WHERE) et non pas par les join.
C'est souvent acquis plus facilement par les élèves et accepté par les examen alors on peut comprendre qu'il déconseillent l'utilisation des join.donc il faut pas taper sur les profs il faut les comprendre.

Bah oui, mais après les jeunes diplômés tombent sur des vieux schnocks autodidactes comme moi, qui les traitons d'ignares et de sagouins, et leur tirons l'oreille en leur demandant "c'est ça que tu as appris à l'école ???"

(et du coup, ils ne savent pas faire une jointure externe

)

Requete SQL avec comptage

Langage SQL

Discussions similaires

Partager

Partager