Aide pour une requête MySQL

Bas

CrazyCow	Aide pour une requête MySQL	3 #1
Je poste trop Inscrit: 29/07/2008 00:26 Post(s): 18991 Karma: 29871	Hello, Je sais que certains d'entre vous sont bien calés sur le sujet et j'aurais besoin d'aide parce que je n'y arrive pas. Contexte : Une table contient les participations de personnes à un QCM. Ils obtiennent un score à chaque participation, et il faut un score minimum de 80 (sur 100) pour que le QCM soit validé. Ce test peut être repassé plusieurs fois. Table (simplifiée) : `Table: qcm +----+----------+-----------+------------+-------+ \| ID \| Lastname \| Firstname \| Date \| Score \| +----+----------+-----------+------------+-------+ \| 1 \| VANCE \| Sophie \| 2019-10-09 \| 90 \| \| 2 \| VANCE \| Sophie \| 2020-10-09 \| 50 \| \| 3 \| AMARI \| Mair \| 2020-10-15 \| 40 \| \| 4 \| ALLAN \| Bryan \| 2020-11-25 \| 100 \| +----+----------+-----------+------------+-------+` Objectif : Avoir une liste avec les mêmes champs, où il n'y a qu'un enregistrement par personne, avec : – Soit la participation réussie la plus récente (ID 1, VANCE) – Soit la participation non réussie la plus récente s'ils n'ont jamais réussi (ID 3, AMARI) Et classée par ordre alphabétique sur Lastname. Résultat attendu : `+----+----------+-----------+------------+-------+ \| ID \| Lastname \| Firstname \| Date \| Score \| +----+----------+-----------+------------+-------+ \| 4 \| ALLAN \| Bryan \| 2020-11-25 \| 100 \| \| 3 \| AMARI \| Mair \| 2020-10-15 \| 40 \| \| 1 \| VANCE \| Sophie \| 2019-10-09 \| 90 \| +----+----------+-----------+------------+-------+` Est-ce que vous savez si c'est possible avec une requête SQL ? Avec la requête suivante je suis arrivé à répondre au premier objectif, mais je ne vois pas comment faire apparaitre aussi ceux qui n'ont jamais eu un score >= 80. `SELECT t1.* FROM qcm t1 WHERE t1.id = (SELECT t2.id FROM qcm t2 WHERE t2.lastname = t1.lastname AND t2.firstname = t1.firstname AND t2.score >= 80 ORDER BY t2.date DESC LIMIT 1) ORDER BY t1.lastname` Merci infiniment pour votre aide Contribution le : 11/12/2020 00:42
	_________________ K TROPHY “ *À un moment donné, il faut lâcher prise.* Claude François
Signaler

-Flo-	Re: Aide pour une requête MySQL	2 #2
Je poste trop Inscrit: 08/01/2005 13:41 Post(s): 15190 Karma: 12562	@CrazyCow Je suis sur ma tablette donc je tape à l’aveugle et je peux pas vérifier (et j’en ai bien chié pour taper du mysql sur le clavier tactile qui essaie de corriger chaque mot ), mais ça devrait fonctionner : select ID, Lastname, Firstname, ifnull(passed.last_date, failed.last_date) as Date, ifnull(passed.last_score, failed.last_score) as Score from qcm left join (select ID, substring_index(group_concat(Date order by Date desc), ",", 1) as last_date, substring_index(group_concat(Score order by Date desc), ",", 1) as last_score from qcm where Score >= 80 group by ID) as passed using(ID) left join (select ID, substring_index(group_concat(Date order by Date desc), ",", 1) as last_date, substring_index(group_concat(Score order by Date desc), ",", 1) as last_score from qcm where Score < 80 group by ID) as failed using(ID) order by Lastname En gros il faut séparer les deux cas (succès ou échec), récupérer pour chacun la date et le score de l’occurence la plus récente de chaque personne (grâce au tri sur la date dans le group_concat), et faire au premier niveau un test pour faire afficher l’echec si le succès n’existe pas. Contribution le : 11/12/2020 01:07
	_________________
Signaler

CrazyCow	Re: Aide pour une requête MySQL	1 #3
Je poste trop Inscrit: 29/07/2008 00:26 Post(s): 18991 Karma: 29871	@-Flo- Merci beaucoup, je comprends le principe. Je vais tester ça EDIT : La requête est valide telle quelle (félicitations déjà pour ça !), mais ça me retourne toutes les participations (l'équivalent de SELECT * FROM qcm). Je suis en train de regarder si c'est de ma faute en modifiant la requête avec les vrais noms de champ. Contribution le : 11/12/2020 01:16
	_________________ K TROPHY “ *À un moment donné, il faut lâcher prise.* Claude François
Signaler

-Flo-

Re: Aide pour une requête MySQL

2 #4

Je poste trop

Inscrit: 08/01/2005 13:41

Post(s): 15190

Karma: 12562

@CrazyCow Ah non c’est logique, c’est de ma faute : il faut à nouveau grouper par ID à la fin pour n’avoir qu’une ligne par id.

Donc

select 
  ID,
  Lastname,
  Firstname,
  ifnull(passed.last_date, failed.last_date) as Date,
  ifnull(passed.last_score, failed.last_score) as Score
from qcm
left join 
(select 
  ID, 
  substring_index(group_concat(Date order by Date desc), ",", 1) as last_date,
  substring_index(group_concat(Score order by Date desc), ",", 1) as last_score
from qcm
where Score >= 80
group by ID) as passed using(ID)
left join 
(select 
  ID, 
  substring_index(group_concat(Date order by Date desc), ",", 1) as last_date,
  substring_index(group_concat(Score order by Date desc), ",", 1) as last_score
from qcm
where Score < 80
group by ID) as failed using(ID)
group by ID
order by Lastname

Contribution le : 11/12/2020 01:29

_________________

Signaler

-Flo-	Re: Aide pour une requête MySQL	2 #5
Je poste trop Inscrit: 08/01/2005 13:41 Post(s): 15190 Karma: 12562	@CrazyCow Ah mais non pardon, je croyais que les ID identifiaient une personne dans ton modèle, mais j’ai mal lu : ils identifient une occurrence de test ! Il serait propre d’avoir un identifiant de personne dans ta table qcm. En son absence, tu peux remplacer tous les « group by ID » par un « group by Lastname ». Mais ça ne fonctionne plus si tu as plusieurs personnes avec le même nom. D’où l’intérêt d’avoir recours à un id pour les identifier. Donc : select ID, Lastname, Firstname, ifnull(passed.last_date, failed.last_date) as Date, ifnull(passed.last_score, failed.last_score) as Score from qcm left join (select ID, substring_index(group_concat(Date order by Date desc), ",", 1) as last_date, substring_index(group_concat(Score order by Date desc), ",", 1) as last_score from qcm where Score >= 80 group by Lastname) as passed using(ID) left join (select ID, substring_index(group_concat(Date order by Date desc), ",", 1) as last_date, substring_index(group_concat(Score order by Date desc), ",", 1) as last_score from qcm where Score < 80 group by Lastname) as failed using(ID) group by Lastname order by Lastname Sinon il faudrait grouper par la Lastname et firstname, mais ça commence à pas mal complexifier, et ça ne te protégerait toujours pas contre de parfaits homonymes. Contribution le : 11/12/2020 01:34
	_________________
Signaler

CrazyCow	Re: Aide pour une requête MySQL	1 #6
Je poste trop Inscrit: 29/07/2008 00:26 Post(s): 18991 Karma: 29871	@-Flo- J'aimerais bien, mais c'est la contrainte justement , je ne peux pas avoir un identifiant unique par personne, par contre dans la vraie table j'ai aussi un champ de date de naissance ce qui permet de réduire à un niveau acceptable les risques d'erreur. C'est un problème bloquant pour la requête ? En ajoutant group by lastname, firstname à la fin (plutôt que id), j'obtiens l'erreur : #1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column... Je crois avoir compris avec les modifications de ta dernière requête. Je teste. Contribution le : 11/12/2020 01:38
	_________________ K TROPHY “ *À un moment donné, il faut lâcher prise.* Claude François
Signaler

-Flo-	Re: Aide pour une requête MySQL	2 #7
Je poste trop Inscrit: 08/01/2005 13:41 Post(s): 15190 Karma: 12562	Oui c’est une limitation récente de mysql, dans le mode par défaut tu ne peux plus demander de colonnes qui ne sont pas le fruit d’une fonction de groupe dans un group by. Tu peux désactiver ces contraintes en faisant un set sql_mode = "" dans ta session. PS : l’ID ne sera pas bon non plus du coup. Pour récupérer celui de la bonne occurrence, il faudra aussi faire un group_concat sur le même principe que score et date. PS2 : En fait il faut tout changer, Même les jointures. Je recommence Contribution le : 11/12/2020 01:42
	_________________
Signaler

CrazyCow	Re: Aide pour une requête MySQL	1 #8
Je poste trop Inscrit: 29/07/2008 00:26 Post(s): 18991 Karma: 29871	@-Flo- OK. Je regarde ça plus en détail demain matin et je te redis si je trouve la solution. Merci beaucoup pour ton aide ! Edit : Est-ce que ça ne simplifierait pas les choses si je créais une vue avec toutes les participations >= 80 et une autre avec celles < 80 ? Edit 2 : #1054 - Champ 'Lastname' inconnu dans from clause Je ne vois pas où est le problème, mais je verrai mieux demain Merci ! Edit 3 : Le problème venait bien de moi. Ça semble fonctionner avec ta dernière requête ! Je suis en train de vérifier que les résultats soient bons. Contribution le : 11/12/2020 01:47
	_________________ K TROPHY “ *À un moment donné, il faut lâcher prise.* Claude François
Signaler

-Flo-

Re: Aide pour une requête MySQL

2 #9

Je poste trop

Inscrit: 08/01/2005 13:41

Post(s): 15190

Karma: 12562

@CrazyCow Donc :

select 
  ifnull(passed.last_id, failed.last_id) as ID
  Lastname,
  Firstname,
  ifnull(passed.last_date, failed.last_date) as Date,
  ifnull(passed.last_score, failed.last_score) as Score
from qcm
left join 
(select 
  Lastname,
  Firstname,
  substring_index(group_concat(ID order by Date desc), ",", 1) as last_id,
  substring_index(group_concat(Date order by Date desc), ",", 1) as last_date,
  substring_index(group_concat(Score order by Date desc), ",", 1) as last_score
from qcm
where Score >= 80
group by Lastname, Firstname) as passed using(Lastname, Firstname)
left join 
(select 
  Lastname,
  Firstname,
  substring_index(group_concat(ID order by Date desc), ",", 1) as last_id,
  substring_index(group_concat(Date order by Date desc), ",", 1) as last_date,
  substring_index(group_concat(Score order by Date desc), ",", 1) as last_score
from qcm
where Score < 80
group by Lastname, Firstname) as failed using(Lastname, Firstname)
group by Lastname, Firstname 
order by Lastname, Firstname

Si ça marche pas, je testerai aussi demain en condition réelle, ça sera plus pratique.

[edit] et sinon pour pas te faire chier avec cette histoire de sql_mode, tu peux enlever le dernier group by et commencer la première ligne par un « select distinct », ça devrait marcher tout aussi bien.

select distinct
  ifnull(passed.last_id, failed.last_id) as ID,
  Lastname,
  Firstname,
  ifnull(passed.last_date, failed.last_date) as Date,
  ifnull(passed.last_score, failed.last_score) as Score
from qcm
left join 
(select 
  Lastname,
  Firstname,
  substring_index(group_concat(ID order by Date desc), ",", 1) as last_id,
  substring_index(group_concat(Date order by Date desc), ",", 1) as last_date,
  substring_index(group_concat(Score order by Date desc), ",", 1) as last_score
from qcm
where Score >= 80
group by Lastname, Firstname) as passed using(Lastname, Firstname)
left join 
(select 
  Lastname,
  Firstname,
  substring_index(group_concat(ID order by Date desc), ",", 1) as last_id,
  substring_index(group_concat(Date order by Date desc), ",", 1) as last_date,
  substring_index(group_concat(Score order by Date desc), ",", 1) as last_score
from qcm
where Score < 80
group by Lastname, Firstname) as failed using(Lastname, Firstname)
order by Lastname, Firstname

@CrazyCow Ok donc là j'ai pu tester "en vrai", et la dernière requête fonctionne parfaitement selon moi. J'ai ajouté une ligne dans le jeu de test pour Sophie Vance où elle a obtenu un meilleur score mais à une date antérieure à son dernier succès, pour valider ce point :

mysql> select * from qcm;
+----+----------+-----------+------------+-------+
| id | lastname | firstname | date      | score |
+----+----------+-----------+------------+-------+
|  1 | VANCE    | Sophie    | 2019-10-09 |    90 |
|  2 | VANCE    | Sophie    | 2020-10-09 |    50 |
|  3 | AMARI    | Mair      | 2020-10-15 |    40 |
|  4 | ALLAN    | Bryan    | 2020-11-25 |  100 |
|  5 | VANCE    | Sophie    | 2019-09-09 |  100 |
+----+----------+-----------+------------+-------+
5 rows in set (0.00 sec)

Et résultat de la requête :

select distinct
  ifnull(passed.last_id, failed.last_id) as id,
  lastname,
  firstname,
  ifnull(passed.last_date, failed.last_date) as date,
  ifnull(passed.last_score, failed.last_score) as score
from qcm
left join 
(select 
  lastname,
  firstname,
  substring_index(group_concat(id order by date desc), ",", 1) as last_id,
  substring_index(group_concat(date order by date desc), ",", 1) as last_date,
  substring_index(group_concat(score order by date desc), ",", 1) as last_score
from qcm
where score >= 80
group by lastname, firstname) as passed using(lastname, firstname)
left join 
(select 
  lastname,
  firstname,
  substring_index(group_concat(id order by date desc), ",", 1) as last_id,
  substring_index(group_concat(date order by date desc), ",", 1) as last_date,
  substring_index(group_concat(score order by date desc), ",", 1) as last_score
from qcm
where score < 80
group by lastname, firstname) as failed using(lastname, firstname)
order by lastname, firstname

+------+----------+-----------+------------+-------+
| id  | lastname | firstname | date      | score |
+------+----------+-----------+------------+-------+
| 4    | ALLAN    | Bryan    | 2020-11-25 | 100  |
| 3    | AMARI    | Mair      | 2020-10-15 | 40    |
| 1    | VANCE    | Sophie    | 2019-10-09 | 90    |
+------+----------+-----------+------------+-------+
3 rows in set (0.00 sec)

Contribution le : 11/12/2020 01:52

_________________

Signaler

CrazyCow	Re: Aide pour une requête MySQL	2 #10
Je poste trop Inscrit: 29/07/2008 00:26 Post(s): 18991 Karma: 29871	@-Flo- Le problème venait bien de moi (j'avais oublié un lastname dans un select). Ça fonctionne parfaitement avec ta dernière requête ! Merci beaucoup pour ton temps Contribution le : 11/12/2020 10:33
	_________________ K TROPHY “ *À un moment donné, il faut lâcher prise.* Claude François
Signaler

-Flo-	Re: Aide pour une requête MySQL	3 #11
Je poste trop Inscrit: 08/01/2005 13:41 Post(s): 15190 Karma: 12562	@CrazyCow Cool ! Pas de souci ! Contribution le : 11/12/2020 10:38
	_________________
Signaler

Haut

S'enregistrer pour contribuer