Sélection multiple avec Mysql

**DuoCow** · 07/09/2016, 00h52

Bonsoir tous le monde,

J'aimerais avoir une aide sur un problème qui me bloque ça fait bientôt deux semaines. Nuits blanches, cheveux arrachés, mais rien n'est fait. je bloque toujours.

Donc voilà,

J'ai deux entités, Machine et pièce. une relation 1 à plusieurs. Chaque machine possède plusieurs pièces et chaque pièce a ses propres caractéristiques (référence, constructeur, nbre de watt ....) .

dans le moteur de recherche, je voudrais sélectionner toutes les machine ayant par exemple 4 pièces.

La capacité de la première pièces serait égale ou supérieur à 200 watt ;

La deuxième serait égale ou supérieure à 250 watt ;

La Troisième aussi égale ou supérieure à 200 watt ;

La quatrième, disons 150 .

Quelle serait donc la requête sql ( Mysql ) pour effectuer une telle recherche ?

Toute aide et toute suggestion serait la bienvenue. Merci beaucoup.

**al1_24** · 07/09/2016, 08h55

Si l'on met bout à bout toutes tes conditions, on obtient cela :

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
select  distinct mch.id_machine
from    machine mch
    inner join
        piece   pc1
        on  mch.id_machine  = pc1.id_machine
    inner join
        piece   pc2
        on  mch.id_machine  = pc2.id_machine
    inner join
        piece   pc3
        on  mch.id_machine  = pc3.id_machine
    inner join
        piece   pc4
        on  mch.id_machine  = pc4.id_machine
where   pc1.id_piece not in (pc2.id_piece, pc3.id_piece, pc4.id_piece)
    and pc2.id_piece not in (pc1.id_piece, pc3.id_piece, pc4.id_piece)
    and pc3.id_piece not in (pc1.id_piece, pc2.id_piece, pc4.id_piece)
    and pc4.id_piece not in (pc1.id_piece, pc2.id_piece, pc3.id_piece)
    and pc1.puisssance  >= 200
    and pc2.puisssance  >= 250
    and pc3.puisssance  >= 200
    and pc4.puisssance  >= 150
    and exists
        (   select  null
            from    piece pcs
            where   mch.id_machine  = pcs.id_machine
            having  count(*)  = 4
        )

Il y a peut-être moyen de faire plus joli, mais ça me semble répondre au problème...

**DuoCow** · 07/09/2016, 22h01

Envoyé par al1_24

Il y a peut-être moyen de faire plus joli, mais ça me semble répondre au problème...

Merci Beaucoup al1_24, c'est exactement ce que je voulais. J'aurais dû vous consulter y a deux semaines.
Merci infiniment pour votre réponse rapide et qui me sauve juste au bon moment.
C'est peut être pas joli comme vous dites, mais ça marche !

Merci encore et bonne soirée

**Artemus24** · 10/09/2016, 02h58

Salut à tous.

J'ai repris la solution proposé par al1_24 afin de l'améliorer.
J'ai comparé la solution de al1_24 (requête 1) à ma solution (requête 2).
Et j'ai fait aussi un explain afin de comparer les performances.

D'après la demande de DuoCow, il y a un ordre à respecter dans la numérotation des pièces, puisqu'il dit :

Envoyé par DuoCow

La capacité de la première pièce serait égale ou supérieur à 200 watt ;
La deuxième serait égale ou supérieure à 250 watt ;
La Troisième aussi égale ou supérieure à 200 watt ;
La quatrième, disons 150 .

Ce qui implique de numéroter les lignes dans l'ordre de l'identifiant de la table pièce.
Or la solution de al1_24 ne tient pas compte de cet ordre.

J'ai créé un jeu d'essai où j'ai volontairement inversé les limites inférieures de la sélection.
Les bonnes solutions sont la "machine 2" et la "machine 4". La mauvaise solution est la "machine 6".

Voici le résultat :

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
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
 
--------------
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 `machine`
--------------
 
--------------
CREATE TABLE `machine`
( `id_machine`  integer unsigned NOT NULL auto_increment primary key,
  `nom`         varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `machine` (`nom`) values ('machine 1'),('machine 2'),('machine 3'),('machine 4'),('machine 5'),('machine 6')
--------------
 
--------------
select * from machine
--------------
 
+------------+-----------+
| id_machine | nom       |
+------------+-----------+
|          1 | machine 1 |
|          2 | machine 2 |
|          3 | machine 3 |
|          4 | machine 4 |
|          5 | machine 5 |
|          6 | machine 6 |
+------------+-----------+
--------------
DROP TABLE IF EXISTS `piece`
--------------
 
--------------
CREATE TABLE `piece`
( `id_piece`      integer unsigned NOT NULL auto_increment primary key,
  `clef_machine`  integer unsigned NOT NULL,
  `nom`           varchar(255)     NOT NULL,
  `capacite`      integer unsigned NOT NULL,
  CONSTRAINT `FK_PIECE_MACHINE` FOREIGN KEY (`clef_machine`) REFERENCES `machine` (`id_machine`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `piece` (`clef_machine`,`nom`,`capacite`) values
  (1, 'piece 1', 150),  (1, 'piece 2', 125),  (1, 'piece 3', 250),  (1, 'piece 4', 375),  (1, 'piece 5', 400),
  (2, 'piece 1', 200),  (2, 'piece 2', 250),  (2, 'piece 3', 200),  (2, 'piece 4', 150),
  (3, 'piece 1', 125),  (3, 'piece 2', 275),  (3, 'piece 3', 225),  (3, 'piece 4', 175),
  (4, 'piece 1', 300),  (4, 'piece 2', 275),  (4, 'piece 3', 225),  (4, 'piece 4', 175),
  (5, 'piece 1', 210),  (5, 'piece 2', 255),  (5, 'piece 3', 225),  (5, 'piece 4', 150),  (5, 'piece 5', 175),
  (6, 'piece 1', 250),  (6, 'piece 2', 200),  (6, 'piece 3', 150),  (6, 'piece 4', 200)
--------------
 
--------------
select * from piece
--------------
 
+----------+--------------+---------+----------+
| id_piece | clef_machine | nom     | capacite |
+----------+--------------+---------+----------+
|        1 |            1 | piece 1 |      150 |
|        2 |            1 | piece 2 |      125 |
|        3 |            1 | piece 3 |      250 |
|        4 |            1 | piece 4 |      375 |
|        5 |            1 | piece 5 |      400 |
|        6 |            2 | piece 1 |      200 |
|        7 |            2 | piece 2 |      250 |
|        8 |            2 | piece 3 |      200 |
|        9 |            2 | piece 4 |      150 |
|       10 |            3 | piece 1 |      125 |
|       11 |            3 | piece 2 |      275 |
|       12 |            3 | piece 3 |      225 |
|       13 |            3 | piece 4 |      175 |
|       14 |            4 | piece 1 |      300 |
|       15 |            4 | piece 2 |      275 |
|       16 |            4 | piece 3 |      225 |
|       17 |            4 | piece 4 |      175 |
|       18 |            5 | piece 1 |      210 |
|       19 |            5 | piece 2 |      255 |
|       20 |            5 | piece 3 |      225 |
|       21 |            5 | piece 4 |      150 |
|       22 |            5 | piece 5 |      175 |
|       23 |            6 | piece 1 |      250 |
|       24 |            6 | piece 2 |      200 |
|       25 |            6 | piece 3 |      150 |
|       26 |            6 | piece 4 |      200 |
+----------+--------------+---------+----------+
--------------
select  distinct mch.id_machine
      from  machine as mch
 
inner join  piece as  pc1
        on  mch.id_machine  = pc1.clef_machine
 
inner join  piece as pc2
        on  mch.id_machine  = pc2.clef_machine
 
inner join  piece as  pc3
        on  mch.id_machine  = pc3.clef_machine
 
inner join  piece as pc4
        on  mch.id_machine  = pc4.clef_machine
 
     where  pc1.id_piece not in (pc2.id_piece, pc3.id_piece, pc4.id_piece)
       and  pc2.id_piece not in (pc1.id_piece, pc3.id_piece, pc4.id_piece)
       and  pc3.id_piece not in (pc1.id_piece, pc2.id_piece, pc4.id_piece)
       and  pc4.id_piece not in (pc1.id_piece, pc2.id_piece, pc3.id_piece)
       and  pc1.capacite  >= 200
       and  pc2.capacite  >= 250
       and  pc3.capacite  >= 200
       and  pc4.capacite  >= 150
       and exists ( select null from piece as pcs where mch.id_machine = pcs.clef_machine having count(*) = 4)
--------------
 
+------------+
| id_machine |
+------------+
|          2 |
|          4 |
|          6 |
+------------+
--------------
explain select  distinct mch.id_machine
      from  machine as mch
 
inner join  piece as  pc1
        on  mch.id_machine  = pc1.clef_machine
 
inner join  piece as pc2
        on  mch.id_machine  = pc2.clef_machine
 
inner join  piece as  pc3
        on  mch.id_machine  = pc3.clef_machine
 
inner join  piece as pc4
        on  mch.id_machine  = pc4.clef_machine
 
     where  pc1.id_piece not in (pc2.id_piece, pc3.id_piece, pc4.id_piece)
       and  pc2.id_piece not in (pc1.id_piece, pc3.id_piece, pc4.id_piece)
       and  pc3.id_piece not in (pc1.id_piece, pc2.id_piece, pc4.id_piece)
       and  pc4.id_piece not in (pc1.id_piece, pc2.id_piece, pc3.id_piece)
       and  pc1.capacite  >= 200
       and  pc2.capacite  >= 250
       and  pc3.capacite  >= 200
       and  pc4.capacite  >= 150
       and exists ( select null from piece as pcs where mch.id_machine = pcs.clef_machine having count(*) = 4)
--------------
 
+----+--------------------+-------+--------+--------------------------+------------------+---------+-----------------------+------+--------------------------------------------------------------+
| id | select_type        | table | type   | possible_keys            | key              | key_len | ref                   | rows | Extra                                                        |
+----+--------------------+-------+--------+--------------------------+------------------+---------+-----------------------+------+--------------------------------------------------------------+
|  1 | PRIMARY            | pc1   | ALL    | PRIMARY,FK_PIECE_MACHINE | NULL             | NULL    | NULL                  |   26 | Using where; Using temporary                                 |
|  1 | PRIMARY            | mch   | eq_ref | PRIMARY                  | PRIMARY          | 4       | base.pc1.clef_machine |    1 | Using where; Using index                                     |
|  1 | PRIMARY            | pc2   | ALL    | PRIMARY,FK_PIECE_MACHINE | NULL             | NULL    | NULL                  |   26 | Using where; Distinct; Using join buffer (Block Nested Loop) |
|  1 | PRIMARY            | pc3   | ALL    | PRIMARY,FK_PIECE_MACHINE | NULL             | NULL    | NULL                  |   26 | Using where; Distinct; Using join buffer (Block Nested Loop) |
|  1 | PRIMARY            | pc4   | ALL    | PRIMARY,FK_PIECE_MACHINE | NULL             | NULL    | NULL                  |   26 | Range checked for each record (index map: 0x3); Distinct     |
|  2 | DEPENDENT SUBQUERY | pcs   | ref    | FK_PIECE_MACHINE         | FK_PIECE_MACHINE | 4       | base.mch.id_machine   |   13 | Using index                                                  |
+----+--------------------+-------+--------+--------------------------+------------------+---------+-----------------------+------+--------------------------------------------------------------+
--------------
select  id_machine
  from  (
 
select            m.id_machine,
                 p1.capacite,
                 count(p2.id_piece)+1 as rang
           from  machine as m
 
left outer join  piece   as p1
             on  p1.clef_machine = m.id_machine
 
left outer join  piece   as p2
             on  p2.clef_machine = m.id_machine
            and  p2.id_piece < p1.id_piece
 
group by m.id_machine, p1.id_piece
having ((rang=1 and capacite>=200)
    or  (rang=2 and capacite>=250)
    or  (rang=3 and capacite>=200)
    or  (rang=4 and capacite>=150))
order by m.id_machine, p1.id_piece
 
) as s
group by  id_machine
  having  count(*) = ( select count(*) from piece as p3 where p3.clef_machine = s.id_machine )
--------------
 
+------------+
| id_machine |
+------------+
|          2 |
|          4 |
+------------+
--------------
explain select  id_machine
  from  (
 
select            m.id_machine,
                 p1.capacite,
                 count(p2.id_piece)+1 as rang
           from  machine as m
 
left outer join  piece   as p1
             on  p1.clef_machine = m.id_machine
 
left outer join  piece   as p2
             on  p2.clef_machine = m.id_machine
            and  p2.id_piece < p1.id_piece
 
group by m.id_machine, p1.id_piece
having ((rang=1 and capacite>=200)
    or  (rang=2 and capacite>=250)
    or  (rang=3 and capacite>=200)
    or  (rang=4 and capacite>=150))
order by m.id_machine, p1.id_piece
 
) as s
group by  id_machine
  having  count(*) = ( select count(*) from piece as p3 where p3.clef_machine = s.id_machine )
--------------
 
+----+--------------------+------------+-------+--------------------------+------------------+---------+-------------------+------+----------------------------------------------------+
| id | select_type        | table      | type  | possible_keys            | key              | key_len | ref               | rows | Extra                                              |
+----+--------------------+------------+-------+--------------------------+------------------+---------+-------------------+------+----------------------------------------------------+
|  1 | PRIMARY            | <derived2> | ALL   | NULL                     | NULL             | NULL    | NULL              | 1560 | Using temporary; Using filesort                    |
|  3 | DEPENDENT SUBQUERY | p3         | ref   | FK_PIECE_MACHINE         | FK_PIECE_MACHINE | 4       | func              |   13 | Using index                                        |
|  2 | DERIVED            | m          | index | NULL                     | PRIMARY          | 4       | NULL              |    6 | Using index; Using temporary; Using filesort       |
|  2 | DERIVED            | p1         | ALL   | FK_PIECE_MACHINE         | NULL             | NULL    | NULL              |   26 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED            | p2         | ref   | PRIMARY,FK_PIECE_MACHINE | FK_PIECE_MACHINE | 4       | base.m.id_machine |   13 | Using where; Using index                           |
+----+--------------------+------------+-------+--------------------------+------------------+---------+-------------------+------+----------------------------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

La solution de al1_24 demande six niveaux d'imbrications tandis que ma solution n'en demande que cinq.
Si au lieu de quatre pièces on devait en sélectionner disons sept, ma solution demandera encore quatre niveaux d'imbrications.
Inversement, la solution de al1_24, dans ce nouveau cas de figure, demandera neuf niveaux d'imbrications.

Dans ma solution, nulle part nous forçons le nombre de pièce à quatre !
L'astuce pour résoudre ce problème repose d'une part sur l'utilisation de "left outer join" qui extrait les lignes que si elles ont été sélectionnées, et d'autre part sur la fait que mon critère de sélection explicite ce que je désire seulement récupérer :

Code :

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

1
2
3
4
having ((rang=1 and capacite>=200)
    or  (rang=2 and capacite>=250)
    or  (rang=3 and capacite>=200)
    or  (rang=4 and capacite>=150))

Comme nous sommes dans une table contenant des lignes, cela signifie que je désire récupérer la première ou la deuxième ou la troisième ou la quatrième ligne.
C'est en comptabilisant les lignes récupérés, donc au maximum quatre lignes, et en comparant au nombre total de lignes (peut être qautre ou plus) dans la table pièce, que je sélectionne ou pas la machine.

Code :

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

having  count(*) = ( select count(*) from piece as p3 where p3.clef_machine = s.id_machine )

En fait l'astuce que j'utilise ici correspond à la division relationnelle.

@+

**DuoCow** · 10/09/2016, 22h26

Salut Artemus24,

En fait la requette de al1_24 me donne le résultat voulu en modifiant

Code :

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

 having  count(*)  = 4

en

Code :

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

 having  count(*)  >= 4

Si l'employé demande les machines ayant (au moins 4 pièces ) dont:
1- au moins une pièce dont la capacité est supérieure ou égale à 200;
2- au moins une pièce dont la capacité est supérieure ou égale à 250;
3- au moins une pièce dont la capacité est supérieure ou égale à 200;
4- au moins une pièce dont la capacité est supérieure ou égale à 150;

Et l'ordre des pièces importe peu dans ce cas.
Je ne sais pas si tu pourrais l'améliorer dans ce sens, parce que tu as raison, question performance c'est pas vraiment ça.

**Artemus24** · 11/09/2016, 08h09

Salut duocow.

Ne te formaliste pas trop. Ce que je fais, c'est juste un exercice de style.

C'est-à-dire réfléchir sur le problème et produire une autre solution afin de ne pas se contenter que d'une seule réponse.

Ton problème est très intéressant mais difficile à résoudre. Pourquoi ? Car il est combinatoire !
Donc tu auras nécessairement un problème de performance.

Je récapitule afin d'être bien d'accord sur le travail à faire :

1) l'ordre n'a pas d'importance.
2) il y a seulement quatre critères.
3) on sélectionne les machines ayant 4 pièces ou plus.

La solution que je propose est différente de ce que al1_24 et moi-même nous vous avons donné précédemment.
Il faut décomposer le problème en deux étapes :

a) sélectionner toutes les lignes en faisant une extraction par critères. Ceci se fera en créant une tables, et en faisant des "union all".

b) ensuite, faire quatre jointures sur cette nouvelle table et trier selon la présence des quatre critères.
Il faudra pour cela quatre identifiants pièces mais tous différents.
Et comme nous risquons d'avoir plusieurs solutions pour une même machine, nous faisons un regroupement sur le critère identifiant machine.

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
--------------
create table sample as
select           m.id_machine, p.id_piece, 1 as critere, p.capacite
           from  machine as m
left outer join  piece   as p
             on  p.clef_machine = m.id_machine
          where  capacite>=200
 
union all
 
select           m.id_machine, p.id_piece, 2 as critere, p.capacite
           from  machine as m
left outer join  piece   as p
             on  p.clef_machine = m.id_machine
          where  capacite>=250
 
union all
 
select           m.id_machine, p.id_piece, 3 as critere, p.capacite
           from  machine as m
left outer join  piece   as p
             on  p.clef_machine = m.id_machine
          where  capacite>=200
 
union all
 
select           m.id_machine, p.id_piece, 4 as critere, p.capacite
           from  machine as m
left outer join  piece   as p
             on  p.clef_machine = m.id_machine
          where  capacite>=150
--------------
 
--------------
select      t1.id_machine,
            t1.id_piece, t1.capacite,
            t2.id_piece, t2.capacite,
            t3.id_piece, t3.capacite,
            t4.id_piece, t4.capacite
 
      from  sample as t1
 
inner join  sample as t2
        on  t2.id_machine = t1.id_machine
       and  t2.critere    > t1.critere
       and  t2.id_piece  <> t1.id_piece
 
inner join  sample as t3
        on  t3.id_machine = t1.id_machine
       and  t3.critere    > t2.critere
       and  t3.id_piece  <> t1.id_piece
       and  t3.id_piece  <> t2.id_piece
 
inner join  sample as t4
        on  t4.id_machine = t1.id_machine
       and  t4.critere    > t3.critere
       and  t4.id_piece  <> t1.id_piece
       and  t4.id_piece  <> t1.id_piece
       and  t4.id_piece  <> t2.id_piece
       and  t4.id_piece  <> t3.id_piece
 
group by t1.id_machine
order by t1.id_machine
--------------
 
+------------+----------+----------+----------+----------+----------+----------+----------+----------+
| id_machine | id_piece | capacite | id_piece | capacite | id_piece | capacite | id_piece | capacite |
+------------+----------+----------+----------+----------+----------+----------+----------+----------+
|          1 |        5 |      400 |        4 |      375 |        3 |      250 |        1 |      150 |
|          2 |        8 |      200 |        7 |      250 |        6 |      200 |        9 |      150 |
|          4 |       16 |      225 |       15 |      275 |       14 |      300 |       17 |      175 |
|          5 |       20 |      225 |       19 |      255 |       18 |      210 |       21 |      150 |
|          6 |       26 |      200 |       23 |      250 |       24 |      200 |       25 |      150 |
+------------+----------+----------+----------+----------+----------+----------+----------+----------+
 
Appuyez sur une touche pour continuer...

Cela produit toutes les combinaisons possibles mais nous retiendrons que la première.

Je n'ai pas mis la création des tables et le jeu d'essai, car cela n'a pas changé.
On constate que cette fois-ci, d'autres machines sont sélectionnées alors que précédemment, ce n'était pas le cas.

Vu que le seul critère est la capacité, il serait bon de mettre un index sur cette colonne.

@+

**aieeeuuuuu** · 15/09/2016, 12h33

Bonjour,

Je pense qu'on peut largement simplifier comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT m.idmachine
FROM	Machine M
INNER JOIN Piece P
	ON P.idmachine = M.idmachine
GROUP BY m.idmachine
HAVING		SUM(CASE WHEN puissance >= 150 THEN 1 ELSE 0 END) >= 4
	AND		SUM(CASE WHEN puissance >= 200 THEN 1 ELSE 0 END) >= 3
	AND		SUM(CASE WHEN puissance >= 250 THEN 1 ELSE 0 END) >= 1

**Artemus24** · 15/09/2016, 15h20

Salut aieeeuuuuu.

Merci de ta participation. Je viens de tester ta solution mais je pense que tu ne simplifies rien du tout.

Il s'agit d'un problème combinatoire et l'on recherche les différentes solutions possibles avec comme contraintes :
--> une pièce ayant une capacité >= 200 watts
--> une deuxième (donc différente de la première) ayant une capacité >= 250 watts
--> une troisième pièce (donc différente de la première et de la deuxième) ayant une capacité >= 200 watts
--> une quatrième pièce (donc différente de la première, de la deuxième et de la troisième) ayant une capacité >= 150 watts

L'ordre d'extraction des pièces n'a aucune importance.
Tandis que l'ordre des contraintes à de l'importance.

@+

**aieeeuuuuu** · 16/09/2016, 10h02

Envoyé par Artemus24

je pense que tu ne simplifies rien du tout.

A première vue, ma requete est tout de même un poil plus simple ! une seule jointure avec un GROUP BY et un HAVING

Envoyé par Artemus24

Il s'agit d'un problème combinatoire et l'on recherche les différentes solutions possibles

Il n'y a rien de combinatoire dans le besoin exprimé, et on ne cherche justement pas toutes les solutions possibles, seulement les machines qui répondent à une série de critères.
Ce serait éventuellement de la combinatoire s'il s'agissait par exemple de trouver quelles sont les pièces d'une machine qui répondent aux critères, tout en minimisant la puissance totale, mais ce n'est visiblement pas le cas.

Envoyé par Artemus24

avec comme contraintes :
--> une pièce ayant une capacité >= 200 watts
--> une deuxième (donc différente de la première) ayant une capacité >= 250 watts
--> une troisième pièce (donc différente de la première et de la deuxième) ayant une capacité >= 200 watts
--> une quatrième pièce (donc différente de la première, de la deuxième et de la troisième) ayant une capacité >= 150 watts
L'ordre d'extraction des pièces n'a aucune importance.

Tu sous-entend que ma requête ne respecte pas tous ces critères ? Dans ce cas, peux-tu préciser le(s)quel(s) ?

Envoyé par Artemus24

Tandis que l'ordre des contraintes à de l'importance.

Pourquoi ? Tel que je l'ai formulé, chaque contrainte "englobe" la précédente, c'est d'ailleurs ce qui permet de simplifier la requete.
Comme tu le dis, l'"ordre" des pièce n' pas d'importance, j'ai donc reformulé le besoin ainsi en français :
- au moins 1 pièce >= 250W (ta deuxième pièce)
- au moins 3 pièces >= 200 W (ta première et ta troisième pièce, en tenant compte de ta deuxième)
- au moins 4 pièces >= 150 W (ta quatrième pièce, en tenant compte des trois autres)

Ce qui aboutit à un SQL plus simple à écrire, mais surtout à exécuter.

**escartefigue** · 16/09/2016, 10h33

Si les 2 requêtés produisent le même résultat, le demandeur pourra vérifier laquelle est la plus performante dans son contexte

Je parierai pour la solution de Aieuuu car la solution d'Artemus24 utilise beaucoup de jointures, dont 3 avec des prédicats "<>" non sargable (non indexables) ce qui peut couter très cher si les volumes sont significatifs.

Cela étant, il est toujours intéressant de comparer plusieurs solutions

**Artemus24** · 16/09/2016, 12h27

Salut aieeeuuuuu.

Quand DuoCow avait exposé son problème, al1_24 et moi-même, nous avions compris que l'ordre des pièces avait de l'importance.
Autrement dit, la première pièce devait respecter le premier critère, puis ensuite la deuxième pièce devait respecter le deuxième critère et ainsi de suite.
Et de plus, nous avions compris qu'il s'agissait des machines ayant exactement quatre pièces.

Dans le message #5, DuoCow décrit un peu mieux ce qu'il recherche. Cela se formule maintenant de la façon suivante :
On recherche une pièce respectant le première critère.
Puis on recherche une nouvelle pièce, différente de la précédente, respectant le deuxième critère, et ainsi de suite.
Le tout, pour des machines ayant au moins quatre pièces.

Envoyé par aieeeuuuuu

A première vue, ma requête est tout de même un poil plus simple !

J'ai repris ce matin votre requête, et en effet, elle est beaucoup plus simple. J'ai compris l'astuce que vous avez utilisé.

Mon erreur est d'avoir recherché les différents solutions (d'où le fait que je considère cela comme combinatoire) et de ne donner que la première solution.
Autrement dit, je fais des calculs qui ne servent à rien, vu que c'est la machine qui intéresse DuoCow et non les résultats intermédiaires.

Code :

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

1
2
3
4
HAVING  SUM(CASE WHEN capacite >= 150 THEN 1 ELSE 0 END) >= 4
   AND  SUM(CASE WHEN capacite >= 200 THEN 1 ELSE 0 END) >= 3
   AND  SUM(CASE WHEN capacite >= 200 THEN 1 ELSE 0 END) >= 2
   AND  SUM(CASE WHEN capacite >= 250 THEN 1 ELSE 0 END) >= 1

@+

**DuoCow** · 10/10/2016, 21h22

Salut à tous,

Effectivement, la requête de aieeeuuuuu est beaucoup plus simple et beaucoup plus rapide.J'ai gardé Néanmoins la première solution proposée étant donné que j'ai eu aussi besoin du total du prix net des pièces. Les prix se trouvant dans une autre table, cela m'était donc impossible avec la solution de aieeeuuuuu.

**aieeeuuuuu** · 14/10/2016, 13h52

Envoyé par DuoCow

Les prix se trouvant dans une autre table, cela m'était donc impossible avec la solution de aieeeuuuuu.

Bah non pourquoi ? il suffit d'ajouter une jointure, et de modifier éventuellement le GROUP BY en conséquence

**DuoCow** · 16/10/2016, 02h55

Non aieeeuuuuu, ta requête me retourne le total de toutes les pièces alors j'ai juste besoin du nombre de pièces demandé et du total de leurs prix.
C'est ce qui m'a amené à utiliser la première solution qui elle, me retourne les machines, les pièces (selon le nombre demandé) ainsi que les prix.
C'est en effet pas très propre ni très performant, mais c'est la seule requête avec laquelle j'obtiens le résultat escompté pour le moment.

**Artemus24** · 16/10/2016, 10h46

Salut duocow.

Désolé mais vous ne parliez pas de cela dans votre premier message.

Quand vous parliez de "La capacité de la première pièces", j'avais compris qu'il y avait un ordre à respecter.
Les solutions proposées par la suite ne tiennent plus compte de cette interprétation.
Donc déjà, il y a un problème d'interprétation et cela influence le résultat final.

Maintenant que vous avez l'excellente solution aieeeuuuuu, vous changez les règles de votre problème.
Comment voulez-vous que l'on devine ce que vous cherchez à faire si dans vos explications, vos omettez le but final ?

Envoyé par duocow

c'est la seule requête avec laquelle j'obtiens le résultat escompté pour le moment.

Et c'est quoi le résultat escompté ?

@+

**DuoCow** · 16/10/2016, 21h46

Salut Artemus24,

Vraiment désolé, mais j'ai pourtant essayé. Je vais encore essayer d'être un peu plus claire.
Voilà, j'ai simplifié au maximum mon formulaire de recherche à fin d'éclaircir un peu plus les choses.

Nom : Sans titre.png
Affichages : 171
Taille : 20,8 Ko

Nom : Sans titre.png
Affichages : 171
Taille : 20,8 Ko

Dans le premier cas, on demande une seule pièce dont la puissance est supérieure ou égale à 100.
Le résultat que je souhaite avoir c'est toutes les machines ayant donc aumoins une pièce supérieure ou égale à 100 PLUS l'une des pièces en question si y en a plusieurs.

machine1 -> pièce1 (puissance >=100)
machine2 -> pièce1 (puissance >=100)
machine3 -> pièce1 (puissance >=100) ....

Dans le second cas, on demande 2 pièces. L'une supérieure ou égale à 100 et l'autre supérieure ou égale à 200 ce qui devrait retouner:

machine1 -> pièce1 (puissance >=100)
-------------> pièce2 (puissance >=200)

machine2 -> pièce1 (puissance >=100)
-------------> pièce2 (puissance >=200)

machine3 -> pièce1 (puissance >=100)
-------------> pièce2 (puissance >=200).....

Et si par exemple maintenant on ajoute une troisième pièce supérieure ou égale à 150, ça donnera :

machine1 -> pièce1 (puissance >=100)
-------------> pièce2 (puissance >=200)
-------------> pièce3 (puissance >=150)

machine2 -> pièce1 (puissance >=100)
-------------> pièce2 (puissance >=200)
-------------> pièce3 (puissance >=150)

machine3 -> pièce1 (puissance >=100)
-------------> pièce2 (puissance >=200)
-------------> pièce3 (puissance >=150).....

Voilà, j'espère que cette fois c'est un peu claire pour vous.
@+

**Artemus24** · 17/10/2016, 10h00

Salut DuoCow.

Envoyé par DuoCow

Vraiment désolé, mais j'ai pourtant essayé.

Il n'est pas aussi facile que ça de s'expliquer sur un problème que l'on ne maîtrise pas bien.
Et le sens des mots n'est pas toujours le même pour ceux qui lisent. Donc, il n'y a pas de quoi être désolé.

Envoyé par DuoCow

Dans le premier cas, on demande une seule pièce dont la puissance est supérieure ou égale à 100.

Jusque là, tout va bien. Dans la requête de aieeeuuuuu, cela va se traduire par :

Code :

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

HAVING  SUM(CASE WHEN capacite >= 100 THEN 1 ELSE 0 END) >= 1

Envoyé par DuoCow

Le résultat que je souhaite avoir c'est toutes les machines ayant donc au moins une pièce supérieure ou égale à 100 PLUS l'une des pièces en question si y en a plusieurs.

Pour toutes les machines, la requête de aieeeuuuuu répond bien à votre attente.

La suite (après le "plus" que j'ai mis en rouge), je ne la comprends pas bien.
Vous ne pouvez pas obtenir plusieurs pièces qui correspondent à votre unique critère, car il y a une seule jointure vers la table "piece".

Envoyé par DuoCow

Dans le second cas, on demande 2 pièces. L'une supérieure ou égale à 100 et l'autre supérieure ou égale à 200 ce qui devrait retouner:

Pareil que dans votre premier cas, vous aurez toutes les machines ayant les deux critères à la fois.

Mais comme dans l'exemple ci-dessus, vous n'aurez qu'une pièce répondant à l'un de vos deux critères.

Envoyé par DuoCow

Et si par exemple maintenant on ajoute une troisième pièce supérieure ou égale à 150, ça donnera :

Même remarque. Vous aurez toutes les machines répondant à tous vos critères.
Mais vous aurez qu'une qu'une seule pièce répondant à vos trois critères.

Envoyé par DuoCow

Voilà, j'espère que cette fois c'est un peu claire pour vous.

Pas vraiment, mais on devine ce qui vous manque.
En fait, vous désirez obtenir autant de pièces qu'il y a de critères. Est-ce bien cela ?

En premier lieu, vous ne pouvez pas avoir la même requête pour un critère, deux critères, trois critères ou quatre critères.
Vous devez construire votre requête en fonction du nombre de critères que vous recherchez. En php, c'est pas très compliqué à faire.

En second lieu, la requête de aieeeuuuuu n'est plus adapté à ce que vous cherchez à faire.
Car vous devez mettre autant de jointure vers la table "pièce" qu'il y a de critères.

Ce qui donne pour 1 critère :

Code :

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

1
2
3
4
5
6
7
8
9
select      m.machine,
            p1.*
      from  machine as m
 
inner join  piece as p1
        on  p1.clef      = m.machine
       and  p1.capacite >= 200
 
  group by  m.machine;

Pour deux critères :

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
select      m.machine,
            p1.*,
            p2.*
      from  machine as m
 
inner join  piece as p1
        on  p1.clef      = m.machine
       and  p1.capacite >= 200
 
inner join  piece as p2
        on  p2.clef      = m.machine
       and  p2.capacite >= 250
       and  p2.piece    <> p1.piece
 
  group by  m.machine;

Pour trois critères :

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
select      m.machine,
            p1.*,
            p2.*,
            p3.*
      from  machine as m
 
inner join  piece as p1
        on  p1.clef      = m.machine
       and  p1.capacite >= 200
 
inner join  piece as p2
        on  p2.clef      = m.machine
       and  p2.capacite >= 250
       and  p2.piece    <> p1.piece
 
inner join  piece as p3
        on  p3.clef      = m.machine
       and  p3.capacite >= 200
       and  p3.piece    <> p1.piece
       and  p3.piece    <> p2.piece
 
  group by  m.machine;

Pour quatre critères :

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
select      m.machine,
            p1.*,
            p2.*,
            p3.*,
            p3.*
      from  machine as m
 
inner join  piece as p1
        on  p1.clef      = m.machine
       and  p1.capacite >= 200
 
inner join  piece as p2
        on  p2.clef      = m.machine
       and  p2.capacite >= 250
       and  p2.piece    <> p1.piece
 
inner join  piece as p3
        on  p3.clef      = m.machine
       and  p3.capacite >= 200
       and  p3.piece    <> p1.piece
       and  p3.piece    <> p2.piece
 
inner join  piece as p4
        on  p4.clef      = m.machine
       and  p4.capacite >= 150
       and  p4.piece    <> p1.piece
       and  p4.piece    <> p2.piece
       and  p4.piece    <> p3.piece
 
  group by  m.machine;

En espérant que cela vous convienne !

@+

**aieeeuuuuu** · 18/10/2016, 11h46

Envoyé par DuoCow

Dans le premier cas, on demande une seule pièce dont la puissance est supérieure ou égale à 100.

Et dans ce cas, si une machine se compose de deux pièces (100 W et 200 W Par exemple) alors doit-elle ressortir ou pas ?

même question si elle a deux pièces de 50 et 100 W ?

**SQLpro** · 18/10/2016, 19h44

Si vous n'aviez pas une telle merde que MySQL, la solution serait d'une élégante simplicité !

Démonstration :

-- les machines :

Code :

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

1
2
3
4
5
6
CREATE TABLE T_MACHINE_MAC
(MAC_ID           INT PRIMARY KEY,
 MAC_NOM          VARCHAR(32));
 
INSERT INTO T_MACHINE_MAC VALUES
(1, 'MOULIPRESSOVAPOMIXEUR'), (2, 'BIGLOTRON');

-- les pièces

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
CREATE TABLE T_PIECE_PCE
(PCE_ID           INT PRIMARY KEY,
 PCE_NOM          VARCHAR(32),
 PCE_PUISSANCE    INT);
 
INSERT INTO T_PIECE_PCE VALUES
(1, 'biglotron', 200), 
(2, 'rapatanior', 300), 
(3, 'zigzornifle', 250), 
(4, 'moulacon', 400), 
(5, 'burinator', 220), 
(6, 'tramoleur', 160), 
(7, 'courbignolgic', 500), 
(8, 'clapotron', 230), 
(9, 'pulivameur', 310), 
(10, 'gromoxiflotar', 300), 
(11, 'putrinomoulin', 280);

-- la demande de montage

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE T_MONTAGE_MTG
(MAC_ID             INT NOT NULL,
 MTG_ORDRE          INT NOT NULL,
 MTG_PUISSANCE_MIN  INT);
 
INSERT INTO T_MONTAGE_MTG VALUES
(1, 1, 200),
(1, 2, 250), 
(1, 3, 200), 
(1, 4, 250);

Et la requête finale :

Code :

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

1
2
3
4
5
6
7
8
WITH T0 AS
( SELECT *, ROW_NUMBER() OVER(PARTITION BY MTG_PUISSANCE_MIN ORDER BY PCE_PUISSANCE) AS P
 FROM   T_MONTAGE_MTG AS G
        JOIN T_PIECE_PCE AS P
             ON G.MTG_PUISSANCE_MIN <= P.PCE_PUISSANCE
 ORDER BY MTG_ORDRE, PCE_PUISSANCE)
SELECT * FROM T0 WHERE P = MTG_ORDRE
ORDER BY MTG_ORDRE

Problème MySQmerde en est à l'age de pierre au niveau du SQL (même pas conforme avec la norme SQL2 datant de 1992 !!!) donc :

vous ne pouvez pas faire de CTE
vous ne pouvez pas faire de fonction fenêtrées

En sus cette solution est dynamique. Elle s’adapte quelque soit le nombre de pièces demandées pour la composition de la machine, alors que les autres solution proposées compte tenue des limitation de MySQmerde sont statiques ce qui oblige à récrire la requête si le nombre de pièces n'est pas constant.

A +

**DuoCow** · 18/10/2016, 21h13

Bonsoir Artemus24

Envoyé par Artemus24

En fait, vous désirez obtenir autant de pièces qu'il y a de critères. Est-ce bien cela ?

Oui Artemus24 , c'est exactement ça !

Envoyé par Artemus24

Vous devez construire votre requête en fonction du nombre de critères que vous recherchez.

Oui, c'est ce que j'ai fait justement.

Envoyé par Artemus24

En espérant que cela vous convienne !

En fait, c'est exactement la requête que j'utilise actuellement qui est presque la même que celle proposée par al1_24 au tout début et que j'ai dû modifier un peu. Et oui, ça me convient du moment où le résultat y est

Sélection multiple avec Mysql

Requêtes MySQL

Discussions similaires

Partager

Partager