Requête mysql complexe

**SQLpro** · 11/12/2018, 13h51

1) la présence de ORDER BY dans une sous requêtes est une mauvaise pratique (dans tous les SGBDR sauf MySQmerde, cette requête aurait planté)
2) le SELECT * est une mauvaise pratique qui conduit tout droit à des performances catastrophiques
3) vous utilisez le NATURAL JOIN qui est une mauvaise pratique .
4) l'utilisation d'un GROUP BY sans calcul d'agrégats ne fait strictement rien que plomber les performances
5) le GROUP BY est incorrecte mais comme vous êtes dans cette saloperie de MySQmerde, il ne vous fait pas d'erreur, et renvoi des résultats au hasard...

Au moins commencez par lire les réponses que l'on vous donne...

**Artemus24** · 12/12/2018, 03h29

Salut à tous.

Je ne sais pas pourquoi mais depuis quelques temps, je ne reçois plus les alertes en fin de journée quand un message a été déposé dans le forum mysql.

Quelques conseils :

1) l'identifiant auto incrémenté de la table se nomme 'id'.
Il est inutile de le nommer autrement. Il sert juste à rendre la ligne unique dans la table.

2) comme l'indique SQL_PRO, c'est une connerie d'utiliser les jointures naturelles.

3) ce que vous demandez, Cleden, c'est une mise en forme de vos données.
Normalement, c'est le travail du langage de programmation et non celui de votre requête.
Mais quand on connait par avance le nombre de colonne à produire, on peut se le permettre.

4) dans le cas qui vous concerne, la mise en forme est lourde à faire.
Il s'agit de la dernière requête. Mais pour ce faire, j'ai dû utiliser une view.

Comme je ne connaissais pas la version que vous utilisez, je suis parti de l'hypothèse que ce n'est pas la version mysql 8.0.

5) voici le résultat attendu.
A vous de compléter la vue afin d'obtenir le résultat que vous attendez :

Code :

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

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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `distributeur`
--------------
 
--------------
CREATE TABLE `distributeur`
( `id`            integer unsigned  NOT NULL auto_increment primary key,
  `nom`           varchar(255)      NOT NULL,
  `categorie`     integer unsigned  NOT NULL,
  `localisation`  integer unsigned  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `distributeur` (`nom`,`categorie`,`localisation`) VALUES
  ('Sodifram Kaweni',           0, 1),
  ('Jumbo Score Hauts Vallons', 0, 1),
  ('Shopi Mamoudzou',           0, 1),
  ('Hyper Discount Kaweni',     0, 1),
  ('Sodicash Bandrélé',         2, 1)
--------------
 
--------------
select * from `distributeur`
--------------
 
+----+---------------------------+-----------+--------------+
| id | nom                       | categorie | localisation |
+----+---------------------------+-----------+--------------+
|  1 | Sodifram Kaweni           |         0 |            1 |
|  2 | Jumbo Score Hauts Vallons |         0 |            1 |
|  3 | Shopi Mamoudzou           |         0 |            1 |
|  4 | Hyper Discount Kaweni     |         0 |            1 |
|  5 | Sodicash Bandrélé         |         2 |            1 |
+----+---------------------------+-----------+--------------+
--------------
DROP TABLE IF EXISTS `produit`
--------------
 
--------------
CREATE TABLE `produit`
( `id`           integer unsigned  NOT NULL auto_increment primary key,
  `designation`  varchar(255)      NOT NULL,
  `poids`        decimal(5,3)      NOT NULL,
  `litrage`      decimal(5,3)      NOT NULL,
  `id_marque`    integer unsigned  NOT NULL,
  `id_categorie` integer unsigned  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `produit` (`designation`,`poids`,`litrage`,`id_marque`,`id_categorie`) VALUES
  ('Riz blanc',                   5.000, 0.000, 1, 7),
  ('Yahourts Vanille',            0.000, 0.100, 2, 2),
  ('Frites Surgelées SteakHouse', 1.100, 0.000, 4, 3)
--------------
 
--------------
select * from `produit`
--------------
 
+----+-----------------------------+-------+---------+-----------+--------------+
| id | designation                 | poids | litrage | id_marque | id_categorie |
+----+-----------------------------+-------+---------+-----------+--------------+
|  1 | Riz blanc                   | 5.000 |   0.000 |         1 |            7 |
|  2 | Yahourts Vanille            | 0.000 |   0.100 |         2 |            2 |
|  3 | Frites Surgelées SteakHouse | 1.100 |   0.000 |         4 |            3 |
+----+-----------------------------+-------+---------+-----------+--------------+
--------------
DROP TABLE IF EXISTS `prix`
--------------
 
--------------
CREATE TABLE `prix`
( `id`              integer unsigned  NOT NULL auto_increment primary key,
  `id_produit`      integer unsigned  NOT NULL,
  `id_distributeur` integer unsigned  NOT NULL,
  `localisation`    integer unsigned  NOT NULL,
  `prix`            decimal(5,2)      NOT NULL,
  `date`            date              NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `prix` (`id_produit`,`id_distributeur`,`localisation`,`prix`,`date`) VALUES
  (1, 2, 1,  10.00, '2018-12-02'),
  (2, 1, 1,   2.00, '2018-12-01'),
  (3, 5, 1,   3.95, '2018-11-23'),
  (1, 3, 1,   5.00, '2018-12-04'),
  (1, 3, 1,   6.80, '2018-12-04'),
  (2, 4, 1,  20.00, '2018-12-04'),
  (3, 1, 1,  17.00, '2018-12-04')
--------------
 
--------------
select * from `prix`
--------------
 
+----+------------+-----------------+--------------+-------+------------+
| id | id_produit | id_distributeur | localisation | prix  | date       |
+----+------------+-----------------+--------------+-------+------------+
|  1 |          1 |               2 |            1 | 10.00 | 2018-12-02 |
|  2 |          2 |               1 |            1 |  2.00 | 2018-12-01 |
|  3 |          3 |               5 |            1 |  3.95 | 2018-11-23 |
|  4 |          1 |               3 |            1 |  5.00 | 2018-12-04 |
|  5 |          1 |               3 |            1 |  6.80 | 2018-12-04 |
|  6 |          2 |               4 |            1 | 20.00 | 2018-12-04 |
|  7 |          3 |               1 |            1 | 17.00 | 2018-12-04 |
+----+------------+-----------------+--------------+-------+------------+
--------------
select           *
 
           from  `prix`         as t1
 
left outer join  `distributeur` as t2
             on  t2.id = t1.id_distributeur
 
left outer join  `produit`      as t3
             on  t3.id = t1.id_produit
--------------
 
+----+------------+-----------------+--------------+-------+------------+------+---------------------------+-----------+--------------+------+-----------------------------+-------+---------+-----------+--------------+
| id | id_produit | id_distributeur | localisation | prix  | date       | id   | nom                       | categorie | localisation | id   | designation                 | poids | litrage | id_marque | id_categorie |
+----+------------+-----------------+--------------+-------+------------+------+---------------------------+-----------+--------------+------+-----------------------------+-------+---------+-----------+--------------+
|  1 |          1 |               2 |            1 | 10.00 | 2018-12-02 |    2 | Jumbo Score Hauts Vallons |         0 |            1 |    1 | Riz blanc                   | 5.000 |   0.000 |         1 |            7 |
|  4 |          1 |               3 |            1 |  5.00 | 2018-12-04 |    3 | Shopi Mamoudzou           |         0 |            1 |    1 | Riz blanc                   | 5.000 |   0.000 |         1 |            7 |
|  5 |          1 |               3 |            1 |  6.80 | 2018-12-04 |    3 | Shopi Mamoudzou           |         0 |            1 |    1 | Riz blanc                   | 5.000 |   0.000 |         1 |            7 |
|  2 |          2 |               1 |            1 |  2.00 | 2018-12-01 |    1 | Sodifram Kaweni           |         0 |            1 |    2 | Yahourts Vanille            | 0.000 |   0.100 |         2 |            2 |
|  6 |          2 |               4 |            1 | 20.00 | 2018-12-04 |    4 | Hyper Discount Kaweni     |         0 |            1 |    2 | Yahourts Vanille            | 0.000 |   0.100 |         2 |            2 |
|  3 |          3 |               5 |            1 |  3.95 | 2018-11-23 |    5 | Sodicash Bandrélé         |         2 |            1 |    3 | Frites Surgelées SteakHouse | 1.100 |   0.000 |         4 |            3 |
|  7 |          3 |               1 |            1 | 17.00 | 2018-12-04 |    1 | Sodifram Kaweni           |         0 |            1 |    3 | Frites Surgelées SteakHouse | 1.100 |   0.000 |         4 |            3 |
+----+------------+-----------------+--------------+-------+------------+------+---------------------------+-----------+--------------+------+-----------------------------+-------+---------+-----------+--------------+
--------------
drop view if exists `vue`
--------------
 
--------------
create view `vue` as
  select           t1.id,
                   t3.designation,
                   t3.poids,
                   t3.litrage
 
             from  `prix`         as t1
 
  left outer join  `distributeur` as t2
               on  t2.id = t1.id_distributeur
 
  left outer join  `produit`      as t3
               on  t3.id = t1.id_produit
--------------
 
--------------
select * from `vue`
--------------
 
+----+-----------------------------+-------+---------+
| id | designation                 | poids | litrage |
+----+-----------------------------+-------+---------+
|  1 | Riz blanc                   | 5.000 |   0.000 |
|  4 | Riz blanc                   | 5.000 |   0.000 |
|  5 | Riz blanc                   | 5.000 |   0.000 |
|  2 | Yahourts Vanille            | 0.000 |   0.100 |
|  6 | Yahourts Vanille            | 0.000 |   0.100 |
|  3 | Frites Surgelées SteakHouse | 1.100 |   0.000 |
|  7 | Frites Surgelées SteakHouse | 1.100 |   0.000 |
+----+-----------------------------+-------+---------+
--------------
commit
--------------
 
--------------
select    designation,
            max(poids_1) as   poids_1,
            max(poids_2) as   poids_2,
            max(poids_3) as   poids_3,
          max(litrage_1) as litrage_1,
          max(litrage_2) as litrage_2,
          max(litrage_3) as litrage_3
 
    from  (  select    designation,
                       case when rang = 1 then poids   else null end as   poids_1,
                       case when rang = 2 then poids   else null end as   poids_2,
                       case when rang = 3 then poids   else null end as   poids_3,
                       case when rang = 1 then litrage else null end as litrage_1,
                       case when rang = 2 then litrage else null end as litrage_2,
                       case when rang = 3 then litrage else null end as litrage_3
 
                 from  (      select  t1.designation,
                                      count(t2.id)          as rang,
                                      any_value(t1.poids)   as poids,
                                      any_value(t1.litrage) as litrage
 
                                from  `vue` as t1
 
                          inner join  `vue` as t2
                                  on  t2.designation  = t1.designation
                                 and  t2.id          <= t1.id
 
                            group by  t1.designation, t1.id
                            order by  t1.designation, t1.id
                       ) as y
 
             group by  designation, rang
          ) as x
 
group by  designation
--------------
 
+-----------------------------+---------+---------+---------+-----------+-----------+-----------+
| designation                 | poids_1 | poids_2 | poids_3 | litrage_1 | litrage_2 | litrage_3 |
+-----------------------------+---------+---------+---------+-----------+-----------+-----------+
| Frites Surgelées SteakHouse |   1.100 |   1.100 |    NULL |     0.000 |     0.000 |      NULL |
| Riz blanc                   |   5.000 |   5.000 |   5.000 |     0.000 |     0.000 |     0.000 |
| Yahourts Vanille            |   0.000 |   0.000 |    NULL |     0.100 |     0.100 |      NULL |
+-----------------------------+---------+---------+---------+-----------+-----------+-----------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

**Watilin** · 12/12/2018, 16h04

Je rappelle que Cleden était parti sur de l’utf8 à la base. Il eût été correct de passer à utf8mb4 au lieu régresser vers latin1.

Il y a sûrement encore beaucoup de choses que j’ignore, peut-être qu’utiliser latin1 donne de meilleures performances, et d’ailleurs ça ne m’étonnerait même pas. Mais si le serveur PHP parle utf-8, il va y avoir inévitablement des problèmes d’encodage. Je sais d’expérience qu’il est impossible d’insérer, par exemple, le caractère « č » de l’alphabet tchèque dans une table en latin1. Et je ne parle même pas des emojis…

**Artemus24** · 12/12/2018, 16h22

Salut Watilin.

Vous êtes du genre à voir l'arbre qui cache la forêt.
Le charset latin1 n'a aucune importance dans la requête que je propose.
C'est juste un choix puisque mon exemple s'exécute avec la console windows.

Le problème de Cleden concerne la présentation des données sous forme de tableau.
Dans l'exemple que j'ai donné, il y a au maximum trois occurrences par ligne.
Maintenant, c'est à Cleden de compléter mon exemple pour obtenir ce qu'il cherche à faire.

@+

**Watilin** · 12/12/2018, 17h05

Envoyé par Artemus24

Vous êtes du genre à voir l'arbre qui cache la forêt.

Un problème d’encodage, c’est quand même un très gros arbre :mgreen:

Le charset latin1 n'a aucune importance dans la requête que je propose.

Il en a une parce qu’on est sur un forum d’entraide et que tu (j’espère que ça ne te dérange pas que je te tutoie) as posté un code complet fonctionnel, qui par ailleurs est propre, utilise de bonnes pratiques et s’accompagne de recommandations avisées. Tu fais en quelque sorte « figure d’autorité »… Et tu utilises latin1. Beaucoup de débutants pourraient prendre ça comme une recommandation à suivre.

Requête mysql complexe

Requêtes MySQL

Partager

Partager