compter les noms absents d'une table en regroupant par type

**gastoncs** · 24/03/2012, 02h02

Bonsoir, voila quelques heure que je ne trouve pas la solution a mon problème, j'ai deux tables,

la première PERSONNELS dont voici une partie de la structure:
grade (varchar)
nom (varchar)
prenom (varchar)
matricule (int)
centre (int)

la deuxième POINTAGE dont voici une partie :
sp (varchar) (contient la concaténation de grade, nom, prenom, matricule de la table PERSONNELS)
fma (varchar) (contient les données d'une autre table)

j'avais besoin d'une requête qui me dise combien de personne on effectuer chaque fma pour ce faire j'ai crée cette requête qui fonctionne :

Code :

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

"SELECT fma, COUNT(DISTINCT sp) AS nbre FROM pointage WHERE sp IN (SELECT CONCAT(personnels.grade,' ', personnels.nom,' ', personnels.prenom,' (', personnels.matricule,')') FROM personnels where personnels.centre = '$lecentre') GROUP BY fma"

mais a présent j'ai besoin de savoir combien de personnes non PAS effectuer chaque fma !
et la je bloque j'ai essayé avec 'NOT IN' mais ma requête ne me retourne rien !!

pour etre plus explicite voir un exemple, dans personnel j'ai :
PIERRE
ALAIN
MARC

dans pointage j'ai :
PIERRE - FMA RCP
ALAIN - FMA DFE
PIERRE - FMA RCP

ainsi la requête que j'ai crée me renvoi :
FMA RCP 1
FMA DFE 1

et ce que je souhaite c'est que la seconde requête me renvoi :
FMA RCP 2 (car marc et alain ne l'on pas faite)
FMA DFE 2 (pierre et marc ne l'on pas faite)

**punkoff** · 24/03/2012, 12h37

Bonjour,

Et vous en êtes où du développement de l'appli ?

Car là le mieux à faire serai de reprendre la conception du projet de A à Z.

Des concaténations de colonne d'autre table dans une nouvelle colonne signifie qu'il vous manque.. des tables d'associations pour faire vos pointages...

Et les requêtes seraient du coup beaucoup plus simple à réaliser que l'usine à gaz que vous êtes en trein de faire !

**gastoncs** · 24/03/2012, 12h47

Je suis au début, mais que voulais vous dire par table d'associations ?

**punkoff** · 24/03/2012, 14h21

Je ne connais pas le fonctionnel vu que vous l'avez pas présenté mais un exemple :

déjà une entité des grades où vous allez référencer les différents grade possible :
grade(gra_nom, gra_description)

Ensuite une entité personnel que l'on va associé à l'entité des grades :
Personnel-1,1----Possede-----0,n-Grade

Passage MPD :
t_grade_gra(gra_id, gra_nom, gra_description)
t_personnel_per(per_id,#gra_id, per_nom, per_nprenom,...)

Concernant le pointage, dans votre table actuel vous avez 2 colonnes, une pour représenter une personne et l'autre je ne sais pas.
On va dire pour l'exemple que l'autre est un lieu :
- une personne peut pointer dans un ou plusieurs lieu
- un lieu peut être associé à plusieurs personne

Niveau MCD :
Personnle-0,n-------Pointe-------0,n-Lieu

Passage MPD :
t_personnel_per(per_id,#gra_id, per_nom, per_nprenom,...)
t_lieu_lie(lie_id, lie_nom, lieu_description)
a_per_lie(#per_id, #lie_id)

Ok, maintenant pour le besoin :
-chercher toutes les personnes ayant déjà pointé :

Code :

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

1
2
3
4
 
select *
from t_personnel_per per
where exists (select 1 from a_per_lie a where a.per_id = per.per_id);

-Chercher toutes les personnes n'ayant pas pointé :

Code :

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

1
2
3
4
 
select *
from t_personnel_per per
where not exists (select 1 from a_per_lie a where a.per_id = per.per_id);

Avec telle méthode (la base de la conception), vous évitez par rapport à votre modèle existant :
- redondance des données : gain de place, requête plus performante, etc
- simplification des requêtes : vous ne travaillez plus qu'avec des clef primaires / clef étrangère => gain de perf, meilleur lisibilité du code, etc

**gastoncs** · 26/03/2012, 15h53

ok, j'ai donc revu les structure de mes tables,

voici pour la table personnels :
id_pers (int, auto incrémenté)
pers_nom (varchar)
pers_prenom (varchar)
pers_grade (pointe sur l'id de la table grade)
pers_matricule (varchar)
pers_centre (pointe sur l'id de la table centre)

voici ma table manœuvres qui recense les manœuvres que le personnel peut faire :
id_fma (int, auto incrémenté)
fma_libelle (varchar)

et enfin ma table pointage qui recense les manœuvres que le personnel à fait à une date précisé :
id_pointer (int, auto incrémenté)
pointer_sp (pointer sur l'id de la table personnels)
pointer_fma (pointe sur l'id de la table manœuvre)
pointer_date (date de la réalisation)

Ainsi j'ai pu faire facilement créer une requête qui permet de compter pour chaque manœuvres le nombre de personne l'ayant réalisé :

Code :

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

1
2
3
4
5
6
SELECT manoeuvres.fma_libelle AS NomFMA, COUNT( DISTINCT pointage.pointer_sp ) AS nbre
FROM pointage
INNER JOIN manoeuvres ON pointage.pointer_fma = manoeuvres.id_fma
WHERE pointage.pointer_sp
IN (SELECT personnels.id_pers FROM personnels)
GROUP BY pointage.pointer_fma

Maintenant punkoff j'ai essayé avec ta méthode NOT EXISTS, et NOT IN, mais la 0 enregistrements, alors que j'ai bien des personnes qui n'ont pas effectuer les manœuvres !
donc voyer vous une erreur dans ma requêtes ?

Code :

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

1
2
3
4
5
6
SELECT manoeuvres.fma_libelle AS NomFMA, COUNT( DISTINCT pointage.pointer_sp ) AS nbre
FROM pointage
INNER JOIN manoeuvres ON pointage.pointer_fma = manoeuvres.id_fma
WHERE pointage.pointer_sp
NOT IN (SELECT personnels.id_pers FROM personnels)
GROUP BY pointage.pointer_fma

**punkoff** · 26/03/2012, 16h04

sans exemple non, il faudrai donner un jeu de donnée d'entrée et le résultat voulu

edit: enfin ceci dit, là vous recherchez les manoeuvres qui n'ont été faites par personne et non les personnes qui n'ont fait strictemetn aucune manoeuvre !

**gastoncs** · 26/03/2012, 16h17

**punkoff** · 26/03/2012, 16h32

Bonjour,

Je pense qu'on peut trouver mieux comme requête mais une piste ...

Code :

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

1
2
3
4
5
 
select a.id_fma, a.fma_libelle , (select count(*) from personnels) - count(distinct b.pointer_sp) as cnt 
from manoeuvre a
left outer join pointage b on a.id_fma = b.pointer_fma
group by a.id_fma, a.fma_libelle

**gastoncs** · 26/03/2012, 16h37

merci c'est déjà une belle piste !

Sympa de votre part

**gastoncs** · 30/03/2012, 15h51

Je reviens sur cet requêtes car j'ai un petite question, ou plutôt a ajout a faire, en effet je souhaite connaitre le nom des manœuvres qui n'ont pas été effectué mais pour une ville donné (dans la table personnels j'ai rajouter un champ 'id_centre')

j'ai donc rajouter les conditions sur id_centre dans les 2 counts :

Code :

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

1
2
3
4
5
SELECT a.id_fma, a.fma_libelle , (SELECT count(*) FROM personnels WHERE personnels.pers_centre =1) - count(DISTINCT b.pointer_sp INNER JOIN 
personnels ON b.pointer_sp=personnels.id_pers WHERE personnels.pers_centre=1) AS cnt 
FROM manoeuvre a
LEFT OUTER JOIN pointage b ON a.id_fma = b.pointer_fma
GROUP BY a.id_fma, a.fma_libelle

Mais Mysql me renvoi une erreur de synthaxe !! est ce du au fait que dans le second count que je soustrait, j'ai rajouter une jointure ?

**gastoncs** · 30/03/2012, 16h49

C'est bon j'ai trouvé mais reste un dernier petit point ;
voila ma requete :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT CONCAT( manoeuvres.fma_module, ' - ', manoeuvres.fma_libelle ) AS Nom, 
((SELECT count( * )FROM personnels WHERE personnels.pers_centre =5) - COUNT( DISTINCT pointage.pointer_sp ))AS nbreNonValide
FROM pointage
INNER JOIN manoeuvres ON pointage.pointer_fma = manoeuvres.id_fma
WHERE pointage.pointer_sp
IN (
SELECT personnels.id_pers
FROM personnels
WHERE personnels.pers_centre =5
)
GROUP BY pointage.pointer_fma

Le soucis est les nom des FMA ne s'affiche que pour celle deja enregistré dans la table pointage, alors que je voudrais afficher touts les manoeuvre présente dans la table manoeuvre, j'ai pensé a faire un LEFT OUTER JOIN mais rien n'y change !

compter les noms absents d'une table en regroupant par type

Requêtes MySQL

Discussions similaires

Partager

Partager