Forum d'entraide PHPFrance

J'ai un problème pour une requête que j'illustrerai par un exemple farfelu. Si je trouve une solution à ce problème, je trouve celle à mon problème plus complexe.

J'ai une table qui contient les champs suivants : user_id, couleur_gilet dans laquelle je note la couleur du gilet que portent chacun des membres de ma famille pendant un mois avec la couleur qui est choisie dans un menu déroulant.

Est-ce que je peux faire une requête qui me donnerait quelque chose du genre:

user_id rouge vert jaune blanc
1 4 12 8 9
2 12 5 14 4
... ... ... ... ...

Je ne comprend pas très bien. Puisque à chaque id est associé une seule couleur, tu es obligé d'avoir quelque chose comme ca :

user_id rouge vert jaune blanc
1 0 0 1 0
2 1 0 0 0

Mais peut être que tu veux avoir le total des gilet porté au court des dernières années ?

Le id est l'identifiant de la personne. À chaque jour j'entre le id de la personne et la couleur de son gilet. À la fin du mois je veux connaitre les statistiques pour chacun des membres de la famille. à savoir combien de fois ils ont mis un gilet rouge, un vert, etc....

Tu vois mon vrai script concerne des infractions au code de vie d'une école et je désire savoir qui sont mes pires récalcitrants! je veux savoir combien de fois ils sont arrivés en retard, combien de fois ils ont été expulsés de la classe, etc..

Je dirais que tu as besoin d'une structure de cette forme :

1) Eleves (ou membres de la famille)
id_eleve
eleve
...

2) infractions (ou couleur de maillot)
id_infraction
infraction
...

3) eleves_infractions (ou membres_maillot)
id_eleve
id_infraction
date_enregistrement

avec ceci, tu devrais pouvoir t'en sortir sans trop de complications, avec des requetes très simples.
Tes tables sont elles sous cette forme?
Si non, est-il envisageable de les modifier?

C'est exactement ce que j'ai. Laissons tomber l'exemple bidon. J'ai effectivement ces trois tables. Je peux obtenir le classement du nombre de retards avec la requête suivante:

SELECT nom, groupe, COUNT(motif)
FROM rapports AS t
INNER JOIN liste_eleves AS s
ON t.id_eleve=s.id_eleve
WHERE motif = "retard"
GROUP BY nom
ORDER BY COUNT(motif) DESC

Ce que je voudrais c'est avoir dans UNE SEULE REQUÊTE le total des retards, des expulsions, des devoirs non faits, etc. Malheureusement, le where me bloque. Devrais-je faire une boucle avec un where associé à une variable pour chaque faute et une pour le total des fautes pour ensuite classer par la somme des fautes ou existe-t-il une solution plus simple?

Tu veux lister un seul élève ou bien tous les élèves ?

Tous les élèves car je veux obtenir le classement des mes plus tannants!... En passant, au cas où ça ne parraitrait pas, je suis directeur d'école!

Je pense que tu es obligé de faire une requète sur la table infractions pour récupérer la liste des infractions existantes pour construire ta requète finale de cette façon :

Code : Tout sélectionner

SELECT l.id_eleve, j1.cnt, j2.cnt, …
FROM liste_eleves as l
	JOIN (SELECT id_eleve, COUNT(*) AS cnt FROM eleves_infractions WHERE id_infraction = id1 GROUP BY id_eleve) AS j1 ON j1.id_eleve = l.id_eleve
	JOIN (SELECT id_eleve, COUNT(*) AS cnt FROM eleves_infractions WHERE id_infraction = id2 GROUP BY id_eleve) AS j2 ON j2.id_eleve = l.id_eleve
	…
ORDER BY j1.cnt DESC, j2.cnt DESC, …, l.id_eleve ASC

Tu construis à la volée tes clauses SELECT, FROM et ORDER
Ici, id1, id2, … sont les ids que tu auras récupérées dans la requète préliminaire.

C'est une requète qui te permet de tout avoir d'un coup, mais seul petit problème, elle est lourde, donc il faut avoir un serveur qui tient la route…

Sinon, après, il y a peut être une autre solution, mais c'est avec des triggers et tout, et c'est beaucoup plus avancé…

J'ai fait le test suivant

Code : Tout sélectionner

SELECT l.id_eleve, j1.retard, j2.exp
FROM liste_eleves as l 
   JOIN (SELECT id_eleve, COUNT(*) AS retard FROM rapports WHERE motif = "Retard" GROUP BY id_eleve) AS j1 ON j1.id_eleve = l.id_eleve 
   JOIN (SELECT id_eleve, COUNT(*) AS exp FROM rapports WHERE motif = "Expulsion" GROUP BY id_eleve) AS j2 ON j2.id_eleve = l.id_eleve 


   ORDER BY j1.retard DESC,l.id_eleve ASC

Avec le premier JOIN, j'obtiens des résultats, si j'ajoute le deuxième, je n'en obtiens plus. Avec un LEFT JOIN j'obtiens des NULL dans la colonne exp. Avec un RIGHT JOIN , j'obtiens des NULL dans les deux premières colonnes.

Des suggestions ?????

Je vois d'où vient le problème, change le GROUP BY par :

Code : Tout sélectionner

GROUP BY id_eleve, motif

Voici ce que j'ai essayé...

Code : Tout sélectionner

SELECT l.id_eleve, j1.retard, j2.exp
FROM liste_eleves as l 
   JOIN (SELECT id_eleve, COUNT(*) AS retard FROM rapports WHERE motif = "Retard" GROUP BY id_eleve, motif) AS j1 ON j1.id_eleve = l.id_eleve 
   JOIN (SELECT id_eleve, COUNT(*) AS exp FROM rapports WHERE motif = "Expulsion" GROUP BY id_eleve, motif) AS j2 ON j2.id_eleve = l.id_eleve 


   ORDER BY j1.retard DESC,l.id_eleve ASC

Même résultat!

Bizarre bizarre. Tu peux poster le shéma des tables et un échantillon de données histoire que l'on puisse tester ?

Voici la structure de la table rapports

Champ Type Interclassement Attributs Null Défaut Extra Action
no int(5) Non auto_increment
id_eleve int(5) Non 0
motif varchar(30) latin1_swedish_ci Non
commentaire text latin1_swedish_ci Non
date date Non 0000-00-00
auteur varchar(30) latin1_swedish_ci Non
retenue int(3) Non 0
fait tinyint(1) Non 0
date_fait date Non 0000-00-00

Et maintenant la table liste_eleves

Champ Type Interclassement Attributs Null Défaut Extra Action
id_eleve smallint(5) Non auto_increment
nom varchar(40) latin1_swedish_ci Non
groupe char(3) latin1_swedish_ci Non
telephone varchar(8) latin1_swedish_ci Non
responsable varchar(30) latin1_swedish_ci Non

Et un petit échantillon des données ? (A moins que tu travailles sur des données privées)

ok!

CREATE TABLE `liste_eleves` (
`id_eleve` smallint(5) NOT NULL auto_increment,
`nom` varchar(40) NOT NULL default '',
`groupe` char(3) NOT NULL default '',
`telephone` varchar(8) NOT NULL default '',
`responsable` varchar(30) NOT NULL default '',
`residence` tinyint(1) NOT NULL default '0',
`hockey` tinyint(1) NOT NULL default '0',
`badminton` tinyint(1) NOT NULL default '0',
`football` tinyint(1) NOT NULL default '0',
`photo` varchar(20) NOT NULL default '',
`casier` smallint(5) NOT NULL default '0',
`village` varchar(25) NOT NULL default '',
PRIMARY KEY (`id_eleve`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=302 ;

--
-- Contenu de la table `liste_eleves`
--

INSERT INTO `liste_eleves` (`id_eleve`, `nom`, `groupe`, `telephone`, `responsable`, `residence`, `hockey`, `badminton`, `football`, `photo`, `casier`, `village`) VALUES
(1, 'Côté, Scott', '91', '555-2222', 'Claire', 0, 0, 0, 0, '', 0, 'Mingan\r'),
(2, 'Beaudin, Denyse', '91', '555-2730', 'Cindy', 0, 0, 0, 0, '', 16, 'Riv.St-Jean\r'),
(3, 'Rosa, Rosie', '91', '555-2241', 'Claudine', 0, 0, 0, 0, '', 17, 'Havre-St-Pierre\r'),
(4, 'Cyr, Marie-Jeanne', '91', '555-3139', 'Martin', 0, 0, 0, 0, '', 18, 'Havre-St-Pierre\r');

et

CREATE TABLE `rapports` (
`no` int(5) NOT NULL auto_increment,
`id_eleve` int(5) NOT NULL default '0',
`motif` varchar(30) NOT NULL default '',
`commentaire` text NOT NULL,
`date` date NOT NULL default '0000-00-00',
`auteur` varchar(30) NOT NULL default '',
`retenue` int(3) NOT NULL default '0',
`fait` tinyint(1) NOT NULL default '0',
`date_fait` date NOT NULL default '0000-00-00',
PRIMARY KEY (`no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;

--
-- Contenu de la table `rapports`
--

INSERT INTO `rapports` (`no`, `id_eleve`, `motif`, `commentaire`, `date`, `auteur`, `retenue`, `fait`, `date_fait`) VALUES
(7, 2, 'Quitter le terrain de l''école', '', '2007-08-28', 'pierre', 0, 0, '0000-00-00'),
(10, 1, 'Quitter le terrain de l''école', '', '2007-08-29', 'Carole', 0, 0, '0000-00-00'),
(11, 1, 'Quitter le terrain de l''école', '', '2007-08-29', 'Carole', 0, 0, '0000-00-00'),
(23, 3, 'Expulsion', ', '2007-08-30', 'martino', 30, 0, '0000-00-00'),
(25, 4, 'Retard', '', '2007-08-30', 'claudiac', 0, 0, '0000-00-00'),
(26, 3, 'Expulsion', '', '2007-08-30', 'paulp', 0, 0, '0000-00-00'),
(27, 3, 'Retard', '', '2007-08-30', 'pierres', 0, 0, '0000-00-00'),
(35, 1, 'Retard', '', '2007-08-31', 'stephaniem', 15, 0, '0000-00-00'),
(36, 4, 'Expulsion', '', '2007-08-31', 'claudiac', 15, 0, '0000-00-00'),
(31, 2, 'Expulsion', '', '2007-08-31', 'valerie', 0, 0, '0000-00-00');

Évidement j'ai trafiqué le tout pour concerver l'anonymat des gens!

Forum d'entraide PHPFrance

Requête un peu trop compliquée pour moi...

Requête un peu trop compliquée pour moi...