PHPfrance

la communauté d'entraide francophone dédiée au PHP

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

Répondre


Cette question est un moyen d’empêcher des soumissions automatisées de formulaires par des robots.
Smileys
:D :) :( :o :shock: :? 8-) :lol: :x :P :oops: :cry: :evil: :twisted: :roll: :wink: :!: :?: :idea: :arrow: :| :mrgreen: =D> #-o =P~ :^o :non: :priere: 8-|
Voir plus de smileys
  Revue du sujet
 

  Étendre la vue Revue du sujet : Requête un peu trop compliquée pour moi...

par Sékiltoyai » 05 sept. 2007, 03:27

Je suis désolé, je n'ai pas pu tester avant, j'ai eu des soucis avec PHP (la distribution que j'ai récupéré supporte apparement mal mysql :shock: et je n'ai pas le temps de le recompiler pour l'instant :) ).
Bref, j'ai donc fait ça à l'ancienne, en ligne de commande, et ça marche parfaitement bien :

Code : Tout sélectionner

mysql> SELECT l.id_eleve, IFNULL(j1.retard, 0) AS retard, IFNULL(j2.exp, 0) AS expulsion, IFNULL(j3.quit,0) AS desertion FROM liste_eleves AS l -> LEFT 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 -> LEFT 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 -> LEFT JOIN (SELECT id_eleve, COUNT(*) AS quit FROM rapports WHERE motif = "Quitter le terrain de l'ecole" GROUP BY id_eleve, motif) AS j3 ON j3.id_eleve = l.id_eleve -> ORDER BY j3.quit DESC, j2.exp DESC, j1.retard DESC,l.id_eleve ASC; +----------+--------+-----------+-----------+ | id_eleve | retard | expulsion | desertion | +----------+--------+-----------+-----------+ | 1 | 1 | 0 | 2 | | 2 | 0 | 1 | 1 | | 3 | 1 | 2 | 0 | | 4 | 1 | 1 | 0 | +----------+--------+-----------+-----------+ 4 rows in set (0.00 sec)
(Attention tout de même, la ligne de commande supportait mal les accent, j'ai dû renommer le 'é' de "école")

J'ai rajouté les LEFT JOIN et les IFNULL pour afficher correctement les 0 (Tous les élèves ne sèchent pas les cours quand même :D ). Je pense que c'est le résultat attendu. Si cela ne marche toujours pas, il faudra me donner la version de ton serveur.

Au fait, bonne rentrée :) (Pas de chance, moi je suis encore en vacances :mrgreen: )

par Sékiltoyai » 03 sept. 2007, 17:45

Ok, merci :)

par dick-perron » 03 sept. 2007, 17:41

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!

par Sékiltoyai » 03 sept. 2007, 17:27

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

par dick-perron » 03 sept. 2007, 17:18

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

par Sékiltoyai » 03 sept. 2007, 17:07

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

par dick-perron » 03 sept. 2007, 17:01

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!

par Sékiltoyai » 03 sept. 2007, 16:53

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

Code : Tout sélectionner

GROUP BY id_eleve, motif

par dick-perron » 03 sept. 2007, 16:41

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 ?????

par Sékiltoyai » 03 sept. 2007, 04:32

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é…

par dick-perron » 03 sept. 2007, 02:05

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!

par Sékiltoyai » 03 sept. 2007, 02:02

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

par dick-perron » 03 sept. 2007, 01:43

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?

par Shrell » 02 sept. 2007, 19:19

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?

par dick-perron » 02 sept. 2007, 17:31

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..