Utilisation de la fonction max( ) sans employer la fonction group by

Version imprimable

15/06/2016, 18h26
mathieupicard

1 pièce(s) jointe(s)

Utilisation de la fonction max( ) sans employer la fonction group by

bonjour a tous
J’étudie en informatique et je suis un cours d'introduction au base de données en cette belle été. Je dois réalisé un travail et l'une des question me cause pas mal de difficulté actuellement. Je vous donne la question et le information qui vont avec.

Question du TP: Donner le nom et le livrable de l'étape actuelle de chaque projet de l'entreprise. (L'étape actuelle est la dernière étape pour laquelle nous avons des données.)

restriction imposé: vous ne devez pas utiliser un GROUP BY.

indices fournies: vous pouvez utiliser la syntaxe: SELECT x, y FROM ... WHERE y = (SELECT MAX(z) FROM ... WHERE...)
Étant donné un identifiant de projet (idProjet), vous voulez trouver la valeur maximale de l'identifiant d'étape (idEtape).

table à utiliser

etape

idEtape nomEtape livrable

1 Démarrage texte

2 Prévision texte

3 Réalisation texte

4 Surveillance texte

5 clôture texte

etapexProjet

idEtape idProjet

1 1

2 1

3 1

4 1

1 2

2 2

3 2

4 2

5 2

1 3

2 3

3 3

résultat

idProjet nomEtape livrable

1 Surveillance texte

2 Clôture texte

3 Réalisation texte

voici le code que j'ai écris et qui fonctionne pas. Je ne dois pas avoir bien compris le code fournie en indice. S'il y a quelqu'un qui peut me guider sur le bon chemin je dirais pas non. Je demande pas la réponse juste une piste pour réussir à voir ou je me trompe dans le code. merci à tous

Code:

select idProjet, nomEtape, livrable from etape natural join etapexProjet where idProjet=(select max(idEtape)from etapexProjet);

Pièce jointe 212871

Salut Mathieu Picard.

Pour répondre à la demande de cet exercice, vous devez utilisez une sous-requête.
La question est comment récupérer l'idEtape le plus grand.
Autrement dit, c'est un "max()" que vous devez appliquer, mais que sur le projet en question et dans la sous-requête.

Ensuite, vous devez faire une jointure et non un produit cartésien entre vos deux tables.
Je ne voie nulle part dans votre exemple la relation liant la table "etapexprojet" à la table "etape".
Je parle du "on" qui est dans la jointure de mon exemple.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
 
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `etape`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `etape`
( `idEtape`   integer unsigned not null auto_increment primary key,
  `nomEtape`  varchar(255)     not null,
  `livrable`  varchar(255)     not null
) engine=innoDB
  default charset=latin1 collate=latin1_general_ci
  row_format=compressed
--------------
 
--------------
insert into `etape` (`nomEtape`,`livrable`) values
  ('Démarrage','texte'),('Prévision','texte'),('Réalisation','texte'),('Surveillance','texte'),('Clôture','texte')
--------------
 
--------------
select * from etape
--------------
 
+---------+--------------+----------+
| idEtape | nomEtape     | livrable |
+---------+--------------+----------+
|       1 | Démarrage    | texte    |
|       2 | Prévision    | texte    |
|       3 | Réalisation  | texte    |
|       4 | Surveillance | texte    |
|       5 | Clôture      | texte    |
+---------+--------------+----------+
--------------
DROP TABLE IF EXISTS `etapexprojet`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `etapexprojet`
( `id`        integer unsigned not null auto_increment primary key,
  `idEtape`   integer unsigned not null,
  `idProjet`  integer unsigned not null,
  unique index `idx` (`idEtape`,`idProjet`)
) engine=innoDB
  default charset=latin1 collate=latin1_general_ci
  row_format=compressed
--------------
 
--------------
insert into `etapexprojet` (`idEtape`,`idProjet`) values
  (1, 1),(2, 1),(3, 1),(4, 1),(1, 2),(2, 2),(3, 2),(4, 2),(5, 2),(1, 3),(2, 3),(3, 3)
--------------
 
--------------
select * from etapexprojet order by id
--------------
 
+----+---------+----------+
| id | idEtape | idProjet |
+----+---------+----------+
|  1 |       1 |        1 |
|  2 |       2 |        1 |
|  3 |       3 |        1 |
|  4 |       4 |        1 |
|  5 |       1 |        2 |
|  6 |       2 |        2 |
|  7 |       3 |        2 |
|  8 |       4 |        2 |
|  9 |       5 |        2 |
| 10 |       1 |        3 |
| 11 |       2 |        3 |
| 12 |       3 |        3 |
+----+---------+----------+
--------------
select tb1.idProjet,
       tb1.idEtape,
       tb2.nomEtape,
       tb2.livrable
 
from       etapexprojet as tb1
inner join etape        as tb2
on tb2.idEtape = tb1.idEtape
 
order by tb1.idProjet, tb1.idEtape
--------------
 
+----------+---------+--------------+----------+
| idProjet | idEtape | nomEtape     | livrable |
+----------+---------+--------------+----------+
|        1 |       1 | Démarrage    | texte    |
|        1 |       2 | Prévision    | texte    |
|        1 |       3 | Réalisation  | texte    |
|        1 |       4 | Surveillance | texte    |
|        2 |       1 | Démarrage    | texte    |
|        2 |       2 | Prévision    | texte    |
|        2 |       3 | Réalisation  | texte    |
|        2 |       4 | Surveillance | texte    |
|        2 |       5 | Clôture      | texte    |
|        3 |       1 | Démarrage    | texte    |
|        3 |       2 | Prévision    | texte    |
|        3 |       3 | Réalisation  | texte    |
+----------+---------+--------------+----------+
--------------
select tb1.idProjet,
       tb2.nomEtape,
       tb2.livrable
 
from       etapexprojet as tb1
inner join etape        as tb2
on tb2.idEtape = tb1.idEtape
 
where tb1.idEtape = (select max(tb3.idEtape) from etapexprojet as tb3 where tb3.idProjet = tb1.idProjet)
 
order by tb1.idProjet
--------------
 
+----------+--------------+----------+
| idProjet | nomEtape     | livrable |
+----------+--------------+----------+
|        1 | Surveillance | texte    |
|        2 | Clôture      | texte    |
|        3 | Réalisation  | texte    |
+----------+--------------+----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Pour bien comprendre cet exercice, vous devez revoir :

1) comment faire une jointure entre deux tables.

2) comment faire une sous-requête corrélée.

3) rechercher le maximum sur un sous-ensemble de ligne.

Quand on ne sait pas comment procéder, on fait le travail étape par étape.
Cela permet de mieux comprendre ce que l'on recherche à faire.
J'ai volontairement dans mon exemple, créé une requête montrant comment sont ordonnées les lignes dans la jointure.
Il est facile alors de comprendre comment récupérer la dernière ligne d'un projet donnée, sachant que cette dernière ligne est le numéro de l'étape le plus grand.

Et voici une autre solution, sans passer par une sous requête corrélée.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
--------------
select tb1.idProjet,
       tb2.nomEtape,
       tb2.livrable
 
from            etapexprojet as tb1
inner join      etape        as tb2
on tb2.idEtape = tb1.idEtape
 
left outer join etapexprojet as tb3
on  tb3.idProjet = tb1.idProjet
and tb3.idEtape  > tb1.idEtape
 
where tb3.idProjet is null
 
order by tb1.idProjet
--------------
 
+----------+--------------+----------+
| idProjet | nomEtape     | livrable |
+----------+--------------+----------+
|        1 | Surveillance | texte    |
|        2 | Clôture      | texte    |
|        3 | Réalisation  | texte    |
+----------+--------------+----------+

16/06/2016, 13h11
aieeeuuuuu

Citation:

Envoyé par mathieupicard

voici le code que j'ai écris et qui fonctionne pas. Je ne dois pas avoir bien compris le code fournie en indice. S'il y a quelqu'un qui peut me guider sur le bon chemin je dirais pas non. Je demande pas la réponse juste une piste pour réussir à voir ou je me trompe dans le code. merci à tous

Code:

select idProjet, nomEtape, livrable from etape natural join etapexProjet where idProjet=(select max(idEtape)from etapexProjet);

Votre sous requête renvoi le MAX(idEtape) tous projets confondus. Il faut la corréler à la requête principale.

Il restera par ailleurs une erreur évidente d'inattention dans la clause WHERE que je vous laisse trouver !
16/06/2016, 17h27
escartefigue

Citation:

Envoyé par Artemus24

Salut Mathieu Picard.
Ensuite, vous devez faire une jointure et non un produit cartésien entre vos deux tables.
Je ne voie nulle part dans votre exemple la relation liant la table "etapexprojet" à la table "etape".

Non car mathieupicard a utilisé une jointure naturelle

Citation:

Envoyé par mathieupicard

voici le code que j'ai écris et qui fonctionne pas. Je ne dois pas avoir bien compris le code fournie en indice. S'il y a quelqu'un qui peut me guider sur le bon chemin je dirais pas non. Je demande pas la réponse juste une piste pour réussir à voir ou je me trompe dans le code. merci à tous

Code:

select idProjet, nomEtape, livrable from etape natural join etapexProjet where idProjet=(select max(idEtape)from etapexProjet);

Or : la jointure naturelle est à proscrire, si votre description de table change, le résultat de votre jointure change et les temps de réponse aussi, il peuvent facilement devenir insupportables. De plus, ca rend la compréhension des critères de jointure difficile
Nombreux sont les sites qui interdisent ce mode de jointure

Pensez à formatter vos requetes, revenez à la ligne, indentez, aérez, la compréhension en sera plus aisée, ce qui incitera les relecteurs à vous aider ;)

etape
idEtape	nomEtape	livrable
1	Démarrage	texte
2	Prévision	texte
3	Réalisation	texte
4	Surveillance	texte
5	clôture	texte

résultat
idProjet	nomEtape	livrable
1	Surveillance	texte
2	Clôture	texte
3	Réalisation	texte