Discussion :

[sat83]

Bonjour,
je n'arrive pas a trouver la requête adéquat a ce que je veut faire, donc je viens demander un peu d'aide! Je vais illustrer avec un exemple simple :

J'ai une table ABCD avec 3 champs numérique ELEVE, EXAMEN, NOTE. Cette table contient par exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
1	1	11
2	1	22
3	1	33
4	1	44
1	2	111
3	2	333
4	2	444
1	3	1111
2	3	2222
5	3	5555

Je souhaite obtenir les résultat de l'examen 3, avec en rappelle les notes obtenues par les élèves lors de l'examen précédant.

Ma requête est la suivante :

Code :

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

1
2
3
4
5
6
select a1.eleve, a1.examen, a1.note, max(a2.examen), a2.note
from ABCD a1, ABCD a2
where a1.examen=3
and a2.examen(+) < 3
and a1.eleve = a2.eleve(+)
group by a1.eleve, a1.examen, a1.note, a2.note

Le résultat est alors :

Code :

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

1
2
3
4
1     3     1111     1     11
1     3     1111     2     111
2     3     2222     1     22
5     3     5555

Alors que je ne voudrais obtenir que les lignes en rouge (le rappel du dernier examen, pas de tous les examens effectués!)

Je pensais que le MAX/GROUP BY répondrais a mon besoin, mais apparemment pas!

Avez vous une idée?

J'espère que j'ai réussi a bien exposé mon problème!

[Ry_Yo]

Je sais pas si ma syntaxe fonctionne partout, mais sous sql server oui :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT 
    a.*,
    b.note
 
FROM ABCD a
       LEFT OUTER JOIN (SELECT * FROM ABCD WHERE examen = 2) b
              ON (a.eleve = b.eleve)
 
WHERE a.examen= 3

résultat :

Code :

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

1
2
3
 
1	3	1111	111
2	3	2222	222

[sat83]

Pour ma part je suis sous ORACLE.

Le but n'est pas de fixer l'examen précédant à 2, mais de prendre l'examen le plus élevé. Si par exemple l'élève n°2 n'as pas participer à l'examen n°2, il faut prendre l'examen n°1. Et dans le cas ou il n'y a pas d'examen précédant (elève n°5), simplement laissé la note de l'examen précédant et juste afficher la note de l'examen demandé.

[Ry_Yo]

2ème essai toujours fonctionnel sous sql sever :

Dans mon test j'ai supprimé volontairement l'enreg suivant :
1 2 111

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT a.eleve, a.examen, a.note, b.examen, b.note
FROM ABCD a
LEFT OUTER JOIN (
	(SELECT c.eleve, c.examen, c.note FROM ABCD c
		INNER JOIN (
			SELECT eleve, max(examen) examen FROM ABCD 
			WHERE examen < 3
			GROUP BY eleve
		) d ON (d.eleve = c.eleve AND d.examen= c.examen))) b
 
	ON (a.eleve = b.eleve)
WHERE a.examen= 3

retour :

Code :

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

1
2
3
4
 
1	3	1111	1	11
2	3	2222	2	222
5	3	5555	NULL	NULL

[Snipah]

On peut également utiliser la fonction LAG pour récupérer les examens et notes précédentes pour chaque élève.
Ensuite, on n'a plus qu'a faire la jointure pour ne garder que le dernier exam.

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT Exam.eleve, Exam.examen, Exam.note, Exam.ExamPrecedent, Exam.NotePrecedente
FROM (
	 SELECT Examen, Eleve, Note, lag(examen) over (partition by eleve order by examen) as ExamPrecedent, lag(note) over (partition by eleve order by examen) as NotePrecedente
	 FROM ABCD
	 )Exam
JOIN (
	 SELECT max(examen) as Examen
	 FROM ABCD
	 ) DernierExam
	 ON Exam.examen = DernierExam.Examen

[misterdi]

Salut,

Je n'ai pas de SGBD sous ma main pour tester, mais plus simplement :

Code :

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

1
2
3
4
5
6
 
SELECT a1.eleve, a1.examen, a1.note, a2.examen, a2.note
FROM ABCD a1, ABCD a2
WHERE a1.examen = 3
AND a2.examen(+) = (SELECT max(a3.examen) FROM ABCD a3 WHERE a3.examen < 3)
AND a1.eleve = a2.eleve(+)

[sat83]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT a.eleve, a.examen, a.note, b.examen, b.note
FROM ABCD a
LEFT OUTER JOIN (
	(SELECT c.eleve, c.examen, c.note FROM ABCD c
		INNER JOIN (
			SELECT eleve, max(examen) examen FROM ABCD 
			WHERE examen < 3
			GROUP BY eleve
		) d ON (d.eleve = c.eleve AND d.examen= c.examen))) b
 
	ON (a.eleve = b.eleve)
WHERE a.examen= 3

Effectivement cette requête semble fonctionné. Par contre, elle me semble déjà complexe pour un cas aussi simple (mon exemple est simplifié au maximum, mon cas réèl est beaucoup plus complexe), et je crains de ne pas pouvoir intégrer cette solution dans mon cas sous peine d'y passé des années et des années! Je n'ai jamais utiliser de INNER ou OUTER JOIN.

La solution de miterdi me tente déjà beaucoup plus, mais malheureusement elle ne fonctionne pas! (la requête ne retourne rien pour l'élève n°5 car il n'y a pas de note précédante)

Quelqu'un a t'il une autre idée, ou une simplification de la requête de Ry_Yo? En attendant une éventuelle autre réponse, je vais quand même essayer de l'intégrer dans mon cas.

Merci en tous cas à tous pour votre aide!

[Snipah]

Je n'ai jamais utiliser de INNER ou OUTER JOIN.

Bah va falloir s'y mettre, c'est plus lisible et c'est la norme je crois

Et sinon, elle sent le poisson ma requete?
Ensuite, si tu nous exposes ton cas concret, on pourra peut etre t'aider à adapter.

[sat83]

Malheureusement je ne peux pas exposer le cas concret qui est un peu trop compliqué.

Mon cas n'a rien a voir avec des élèves et des notes. J'ai donc simplifier au maximum le problème et choisis un exemple pour illustrer le problème. Mon but étant de comprendre la solution, puis de l'appliquer à mon problème.

Concernant ta requête Snipah, elle me semble plus complexe que celle fournie Ry_Yo, mais elle fonctionne également. Je vais donc essayer pour le moment de comprendre celle de Ry_Yo et de l'appliquer a mon problème.

Pour les INNER OUTER JOIN, j'ai toujours réussi pour le moment à me débrouillé sans, donc si quelqu'un à une équivalence sans, je suis preneur!

Merci.

[Waldar]

Non vous faites erreur, la requête de Snipah est plus simple et plus efficace : deux appels à la table contre trois appels à la table et une jointure externe.

De plus, il n'y a pas de code en dur.

[Ry_Yo]

Bonjour,

voici mon raisonnement pour construire la requête :
- faire des requête simple répondant à 1 seul critère
- puis de les imbriquer avec des jointures

du coup j'ai d'abord fait un premier SELECT pour connaitre quel est le dernier exam passé par les élèves sauf l'exam en cours :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT 
      eleve,
      max(examen) examen 
 
FROM ABCD 
WHERE examen < 3
GROUP BY eleve

Puis j'ai crée une autre requête récupérant IdEleve et la note qu'il a eu au dernier examen (hors celui en cours), donc l'exam récupéré de la première requête.

Code :

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

1
2
3
4
5
6
7
 
SELECT c.eleve, c.examen, c.note 
FROM ABCD c INNER JOIN (
			SELECT eleve, max(examen) examen FROM ABCD 
			WHERE examen < 3
			GROUP BY eleve
		) d ON (d.eleve = c.eleve AND d.examen= c.examen))

Enfin j'ai fait un LEFT OUTER par rapport à la requête principal qui était de connaitre les notes des élèves ayant participé à l'exam en cours...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT a.eleve, a.examen, a.note, b.examen, b.note
FROM ABCD a
LEFT OUTER JOIN (
	(SELECT c.eleve, c.examen, c.note FROM ABCD c
		INNER JOIN (
			SELECT eleve, max(examen) examen FROM ABCD 
			WHERE examen < 3
			GROUP BY eleve
		) d ON (d.eleve = c.eleve AND d.examen= c.examen))) b
 
	ON (a.eleve = b.eleve)
WHERE a.examen= 3

[Snipah]

Si t'es pas à l'aise avec les jointures, voici la version sans, en codant en dur le numéro d'examen voulu comme dans la requete fournie par Ry_Yo.
La fonction lag() n'a rien de compliqué une fois qu'on a pris le temps de comprendre le concept, tu lui donnes un champs, un ordre et si besoin un groupe, et il te renvoie la valeur précédente. Il existe également lead() pour faire l'inverse, et dans certains cas ça simplifie pas mal la vie. Tu peux trouver de bons tuto la dessus sur le site.
Enfin c'est ton appli, donc c'est à toi de choisir

Code :

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

1
2
3
4
5
6
SELECT Exam.eleve, Exam.examen, Exam.note, Exam.ExamPrecedent, Exam.NotePrecedente
FROM (
	 SELECT Examen, Eleve, Note, lag(examen) over (partition by eleve order by examen) as ExamPrecedent, lag(note) over (partition by eleve order by examen) as NotePrecedente
	 FROM ABCD
	 )Exam
where Exam.examen=3

Edit : et je plussoie Waldar sur les perfs, sur une grosse volumétrie ça doit pas être négligeable.

[Ry_Yo]

@Snipah : Au passage, merci j'ai appris 2 nouveaux truc grace à toi aujourd'hui : LAG() et OVER()... Me reste plus qu'à bien les comprendre....

Par contre est-ce que lag() et over() existe aussi sous Oracle ?

[Snipah]

Oui, par contre je saurai pas dire à partir de quelle version... mais 9i au moins.

[Waldar]

LEAD et LAG ont été introduite dans la norme SQL:2008, mais existent sous Oracle depuis une dizaine d'années.

Attention dans les versions pré-10g, je crois qu'il faut une Enterprise Edition pour pouvoir utiliser ces fonctions.
Post 10g elles sont accessibles en Standard Edition.

C'est ce qu'on appelle des fonctions analytiques, ou fonctions de fenêtrages, vous trouverez pas mal d'informations sur développez.net ou sur votre moteur de recherche favori.

[sat83]

Ok, merci à tous pour votre aide!

Je vais tenter d'analyser tous ça et de l'appliquer dans mon cas concret!