Soustraction entre 2 requetes / Erreur SQL (1241):Operand should contain 1 column(s)

**Artemus24** · 23/04/2018, 17h02

Salut

Pour la première requête, voici ce que je trouve en tenant compte de "fecha <= '2018-04-17'".

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
--------------
drop view if exists `prod`
--------------
 
--------------
create view `prod` as
  SELECT    Nave,
            Producto,
            CONCAT(Longitud, 'x', Anchura) AS TAMANO,
            Espesor,
            Seleccion,
            Marcado,
            Formato,
            Particularidad,
            Codigo_referencia,
            SUM(Piezas)                    AS TOTALPIEZAS,
            COUNT(*)                       AS TOTALPALES
  FROM      Produccion
  WHERE    (Lote_cliente = ''
     OR     Lote_cliente IS NULL)
    and     fecha <= '2018-04-17'
  GROUP BY  Nave, Producto, TAMANO, Espesor, Seleccion, Marcado, Formato, Particularidad, Codigo_referencia, Lote_cliente
  ORDER BY  Nave, Producto, TAMANO, Espesor, Seleccion, Marcado, Formato, Particularidad, Codigo_referencia, Lote_cliente
--------------
 
--------------
select * from prod
--------------
 
+---------+----------+---------+---------+-----------+---------+-------------+----------------+-------------------+-------------+------------+
| Nave    | Producto | TAMANO  | Espesor | Seleccion | Marcado | Formato     | Particularidad | Codigo_referencia | TOTALPIEZAS | TOTALPALES |
+---------+----------+---------+---------+-----------+---------+-------------+----------------+-------------------+-------------+------------+
| Atelier | PZ       | 27x18   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       74784 |         26 |
| Atelier | PZ       | 30x20   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |        4118 |          2 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       35174 |         19 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       16000 |          8 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       36536 |         24 |
| Atelier | PZ       | 32x22   | 7       | P         | CE      | Rectangular |           NULL | HH7P              |        8032 |          7 |
| Atelier | PZ       | 35x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |        1492 |          1 |
| Atelier | PZ       | 35x25   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       15694 |         11 |
| Atelier | PZ       | 40x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       38834 |         25 |
| Atelier | PZ       | 40x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |        1518 |          1 |
| Atelier | PZ       | 50x25   | 5       | C         | CE      | Rectangular |             19 | HH5C              |        5726 |          8 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |        5760 |          7 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        3666 |          7 |
+---------+----------+---------+---------+-----------+---------+-------------+----------------+-------------------+-------------+------------+
 
Appuyez sur une touche pour continuer...

Et voici la seconde requête :

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
--------------
drop view if exists `cliente`
--------------
 
--------------
create view `cliente` as
  SELECT      p.Nave,
              p.Producto,
              CONCAT(p.Longitud, 'x', p.Anchura) AS TAMANO,
              p.Espesor,
              p.Seleccion,
              p.Marcado,
              p.Formato,
              p.Particularidad,
              p.Codigo_referencia,
              SUM(p.Piezas)                      AS TOTALPIEZAS,
              COUNT(*)                           AS TOTALPALES
 
        FROM  Lote_Cliente as l
  INNER JOIN  Produccion   as p
          ON  p.Lote_Cliente = l.numero_lote_cliente
       WHERE  l.fecha_preparacion <= '2018-04-17'
    GROUP BY  Nave, Producto, TAMANO, Espesor, Seleccion, Marcado, Formato, Particularidad, Codigo_referencia, Lote_cliente
    ORDER BY  Nave, Producto, TAMANO, Espesor, Seleccion, Marcado, Formato, Particularidad, Codigo_referencia, Lote_cliente
--------------
 
--------------
select * from `cliente`
--------------
 
+---------+----------+---------+---------+-----------+---------+-------------+----------------+-------------------+-------------+------------+
| Nave    | Producto | TAMANO  | Espesor | Seleccion | Marcado | Formato     | Particularidad | Codigo_referencia | TOTALPIEZAS | TOTALPALES |
+---------+----------+---------+---------+-----------+---------+-------------+----------------+-------------------+-------------+------------+
| Atelier | PZ       | 30x20   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       10162 |          5 |
| Atelier | PZ       | 30x20   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       17700 |          9 |
| Atelier | PZ       | 30x20   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       19384 |         10 |
| Atelier | PZ       | 30x20   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       19506 |         10 |
| Atelier | PZ       | 30x20   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |       16572 |          8 |
| Atelier | PZ       | 30x20   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |       10330 |          5 |
| Atelier | PZ       | 30x20   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |       14066 |          7 |
| Atelier | PZ       | 30x20   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       21524 |         10 |
| Atelier | PZ       | 30x20   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |        4274 |          2 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        3600 |          2 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        5400 |          3 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       12600 |          7 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       12600 |          7 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        5400 |          3 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       10800 |          6 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       32400 |         18 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       32400 |         18 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       10800 |          6 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       12600 |          7 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       32400 |         18 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       32400 |         18 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       33520 |         18 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       33576 |         18 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       33620 |         18 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        3744 |          2 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       32400 |         18 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        7380 |          4 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       16200 |          9 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        7416 |          4 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        7450 |          4 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        7364 |          4 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       32974 |         18 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        7534 |          4 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        7500 |          4 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       14956 |          8 |
| Atelier | PZ       | 32x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       18660 |         10 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       14000 |          7 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       18000 |          9 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       18000 |          9 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       22000 |         11 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       36000 |         18 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       12000 |          6 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       22000 |         11 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       36000 |         18 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |        6000 |          3 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       18000 |          9 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       36000 |         18 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       36000 |         18 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       18000 |          9 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       20000 |         10 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       18000 |          9 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       26000 |         13 |
| Atelier | PZ       | 32x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       24000 |         12 |
| Atelier | PZ       | 32x22   | 4       | S         | CE      | Rectangular |           NULL | HH4S              |        1900 |          1 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |        3800 |          2 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       11400 |          6 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       34200 |         18 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       17100 |          9 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       17100 |          9 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       11400 |          6 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |        9500 |          5 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       34200 |         18 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       34200 |         18 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       34200 |         18 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       34200 |         18 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       19000 |         10 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       34200 |         18 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       15200 |          8 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       34200 |         18 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |        5542 |          3 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       34200 |         18 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       34200 |         18 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       17100 |          9 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       17100 |          9 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       17100 |          9 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       19000 |         10 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |        5700 |          3 |
| Atelier | PZ       | 32x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       11400 |          6 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       15224 |         10 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |        9106 |          6 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       12022 |          8 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |        4512 |          3 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |        6124 |          4 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       16690 |         11 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       16678 |         11 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       16708 |         11 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       16562 |         11 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |        6078 |          4 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       16538 |         11 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       10556 |          7 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       29132 |         19 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |        9078 |          6 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       22442 |         15 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       21032 |         14 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       20986 |         14 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       16520 |         11 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       21030 |         14 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       21078 |         14 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       12214 |          8 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       12198 |          8 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       21082 |         14 |
| Atelier | PZ       | 32x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       21252 |         14 |
| Atelier | PZ       | 35x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |       22206 |         13 |
| Atelier | PZ       | 35x22   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       14200 |          7 |
| Atelier | PZ       | 35x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |        1944 |          1 |
| Atelier | PZ       | 35x22   | 4       | S         | NF-D2   | Rectangular |           NULL | HH4S              |       19056 |         10 |
| Atelier | PZ       | 35x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       10890 |          7 |
| Atelier | PZ       | 35x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |        4512 |          3 |
| Atelier | PZ       | 35x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       10908 |          7 |
| Atelier | PZ       | 35x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |        4618 |          3 |
| Atelier | PZ       | 35x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       15148 |         10 |
| Atelier | PZ       | 35x25   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       14698 |         10 |
| Atelier | PZ       | 35x25   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |        8778 |          6 |
| Atelier | PZ       | 35x25   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |        8672 |          6 |
| Atelier | PZ       | 40x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        4372 |          3 |
| Atelier | PZ       | 40x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        3342 |          2 |
| Atelier | PZ       | 40x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        1530 |          1 |
| Atelier | PZ       | 40x22   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        8386 |          5 |
| Atelier | PZ       | 40x22   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |       10854 |          6 |
| Atelier | PZ       | 40x22   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |        7332 |          4 |
| Atelier | PZ       | 40x22   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |       11102 |          6 |
| Atelier | PZ       | 40x22   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |       24378 |         13 |
| Atelier | PZ       | 40x22   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |       14942 |          8 |
| Atelier | PZ       | 40x22   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |       10926 |          6 |
| Atelier | PZ       | 40x22   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |        9114 |          5 |
| Atelier | PZ       | 40x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |        5598 |          4 |
| Atelier | PZ       | 45x22   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |       18178 |         15 |
| Atelier | PZ       | 45x22   | 5       | C         | CE      | Rectangular |             17 | HH5C              |        8984 |          7 |
| Atelier | PZ       | 45x22   | 5       | MX        | CE      | Rectangular |             17 | HH5M              |       21766 |         15 |
| Atelier | PZ       | 45x22   | 5       | MX        | CE      | Rectangular |             17 | HH5M              |       10506 |          8 |
| Atelier | PZ       | 45x22   | 5       | MX        | CE      | Rectangular |             17 | HH5M              |       21046 |         15 |
| Atelier | PZ       | 45x22   | 5       | MX        | CE      | Rectangular |             17 | HH5M              |       20700 |         15 |
| Atelier | PZ       | 45x22   | 5       | MX        | CE      | Rectangular |             17 | HH5M              |       16366 |         12 |
| Atelier | PZ       | 45x22   | 5       | MX        | CE      | Rectangular |             17 | HH5M              |       20448 |         15 |
| Atelier | PZ       | 45x22   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |        4200 |          3 |
| Atelier | PZ       | 45x33   | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         554 |          1 |
| Atelier | PZ       | 45x33   | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1180 |          2 |
| Atelier | PZ       | 45x33   | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1242 |          2 |
| Atelier | PZ       | 45x33   | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         636 |          1 |
| Atelier | PZ       | 45x33   | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1264 |          2 |
| Atelier | PZ       | 46x25   | 5       | P         | CE      | Rectangular |           NULL | HH5P              |         950 |          1 |
| Atelier | PZ       | 46x30   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        3386 |          4 |
| Atelier | PZ       | 46x30   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |        1640 |          2 |
| Atelier | PZ       | 46x30   | 4       | C         | CE      | Rectangular |           NULL | HH4C              |         838 |          1 |
| Atelier | PZ       | 46x30   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |         986 |          1 |
| Atelier | PZ       | 46x30   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |       12924 |         13 |
| Atelier | PZ       | 46x30   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |        4926 |          5 |
| Atelier | PZ       | 46x30   | 4       | MX        | NF-D2   | Rectangular |           NULL | HH4M              |        9832 |         10 |
| Atelier | PZ       | 50x25   | 5       | C         | CE      | Rectangular |           NULL | HH5C              |         762 |          1 |
| Atelier | PZ       | 50x25   | 5       | C         | CE      | Rectangular |             19 | HH5C              |         792 |          1 |
| Atelier | PZ       | 50x25   | 5       | C         | CE      | Rectangular |             19 | HH5C              |       14310 |         19 |
| Atelier | PZ       | 50x25   | 5       | C         | CE      | Rectangular |             19 | HH5C              |       14074 |         19 |
| Atelier | PZ       | 50x25   | 5       | C         | CE      | Rectangular |             19 | HH5C              |       13828 |         19 |
| Atelier | PZ       | 50x25   | 5       | C         | CE      | Rectangular |             19 | HH5C              |       13790 |         19 |
| Atelier | PZ       | 50x25   | 5       | C         | CE      | Rectangular |             19 | HH5C              |       13426 |         19 |
| Atelier | PZ       | 50x25   | 5       | C         | CE      | Rectangular |             19 | HH5C              |       13824 |         19 |
| Atelier | PZ       | 50x25   | 5       | C         | CE      | Rectangular |             19 | HH5C              |       13992 |         19 |
| Atelier | PZ       | 50x25   | 5       | C         | CE      | Rectangular |             19 | HH5C              |       13778 |         19 |
| Atelier | PZ       | 50x25   | 5       | C         | CE      | Rectangular |             19 | HH5C              |        8484 |         12 |
| Atelier | PZ       | 50x25   | 5       | C         | CE      | Rectangular |             19 | HH5C              |       12654 |         18 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |       14708 |         17 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |       17216 |         20 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |       17372 |         20 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |       16626 |         19 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |        4370 |          5 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       16546 |         19 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       14976 |         18 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       14936 |         18 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       16420 |         19 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       16384 |         19 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       16308 |         19 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       15444 |         18 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       15540 |         18 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       15498 |         18 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       15706 |         18 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       15708 |         18 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       15570 |         18 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       15552 |         19 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       14778 |         18 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       12122 |         15 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       15986 |         19 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       15744 |         19 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       15954 |         19 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |        5408 |          7 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       14940 |         18 |
| Atelier | PZ       | 50x25   | 5       | MX        | CE      | Rectangular |             19 | HH5M              |       15734 |         19 |
| Atelier | PZ       | 50x25   | 5       | P         | CE      | Rectangular |             19 | HH5P              |        9672 |         12 |
| Atelier | PZ       | 50x37,5 | 5       | C         | CE      | Rectangular |           NULL | HH5C              |         498 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         542 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         536 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         524 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1636 |          3 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1036 |          2 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1084 |          2 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         534 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         530 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         536 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         540 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1036 |          2 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1048 |          2 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         506 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1046 |          2 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         486 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         528 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         502 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1070 |          2 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1040 |          2 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         504 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1054 |          2 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         512 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1016 |          2 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1018 |          2 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         524 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         514 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         532 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         530 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         540 |          1 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1058 |          2 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |        1030 |          2 |
| Atelier | PZ       | 50x37,5 | 5       | MX        | CE      | Rectangular |           NULL | HH5M              |         522 |          1 |
+---------+----------+---------+---------+-----------+---------+-------------+----------------+-------------------+-------------+------------+
 
Appuyez sur une touche pour continuer...

Est-ce que ces deux requêtes sont conformes à vos attentes ?

@+

**superthx** · 23/04/2018, 19h20

Je test demain.... En tout cas merci, je reviens bientôt...

**superthx** · 24/04/2018, 12h08

Bonjour,
Cela ne passe pas.....
Je m'explique et j’espère du mieux que je peux...

ci joint le fichier sql test.zip

Sur la premiere requete. je change la date pour l'exemple. Et que sur TAMANO 27x18.

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
SELECT    Nave,
            Producto,
            CONCAT(Longitud, 'x', Anchura) AS TAMANO,
            Espesor,
            Seleccion,
            Marcado,
            Formato,
            Particularidad,
            Codigo_referencia,
            SUM(Piezas)                    AS TOTALPIEZAS,
            COUNT(*)                       AS TOTALPALES
  FROM      Produccion
  WHERE    (Lote_cliente = ''
     OR     Lote_cliente IS NULL)
    and     fecha <= '2018-04-19'
  GROUP BY  Nave, Producto, TAMANO, Espesor, Seleccion, Marcado, Formato, Particularidad, Codigo_referencia, Lote_cliente
  ORDER BY  Nave, Producto, TAMANO, Espesor, Seleccion, Marcado, Formato, Particularidad, Codigo_referencia, Lote_cliente

J'ai pour le 2018-04-19:

Code :

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

1
2
3
4
+---------+----------+---------+---------+-----------+---------+-------------+----------------+-------------------+-------------+------------+
| Nave    | Producto | TAMANO  | Espesor | Seleccion | Marcado | Formato     | Particularidad | Codigo_referencia | TOTALPIEZAS | TOTALPALES |
+---------+----------+---------+---------+-----------+---------+-------------+----------------+-------------------+-------------+------------+
| Atelier | PZ       | 27x18   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       74784 |         26 |

Je duplique 2 lignes en Produccion de 27x18 avec une de1000 pieces a la date du 2018-04-20 et l'autre de 2018-04-21 de 2000 pieces et j'ai avec la même requête au 2018-04-21:

Code :

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

1
2
3
4
+---------+----------+---------+---------+-----------+---------+-------------+----------------+-------------------+-------------+------------+
| Nave    | Producto | TAMANO  | Espesor | Seleccion | Marcado | Formato     | Particularidad | Codigo_referencia | TOTALPIEZAS | TOTALPALES |
+---------+----------+---------+---------+-----------+---------+-------------+----------------+-------------------+-------------+------------+
| Atelier | PZ       | 27x18   | 4       | P         | NF-D1   | Rectangular |           NULL | HH4P              |       77784 |         28 |

Donc le 2018-04-19 j'ai 26 TOTALPALES et le 2018-04-20 j'ai 27 TOTALPALES. Attention, ils ne sont pas sortie du stock car dans leurs colonnes Lote_cliente dans la table Produccion la valeur est null

Maintenant je rajoute dans la table Lote_Cliente un client fictif "Toto" avec numero_lote_cliente 18/test et fecha_preparacion = '2018-04-20'
Mettons le numero client 18/test a la palette de 27x18 de 1000 pieces cree precedement dans Lote_cliente dans la table Produccion.

Procedons donc a la requete final au 2018-04-19:

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
select           p1.*,
                 p2.*,
                 p1.totalPiezas - coalesce(p2.totalPiezas,0) as DiffPiezas,
                 p1.totalPales  - coalesce(p2.totalPales,0)  as DiffPales
           from  (  SELECT    Nave,
            Producto,
            CONCAT(Longitud, 'x', Anchura) AS TAMANO,
            Espesor,
            Seleccion,
            Marcado,
            Formato,
            Particularidad,
            Codigo_referencia,
            Producto_tipo,
            SUM(Piezas)                    AS TOTALPIEZAS,
            COUNT(*)                       AS TOTALPALES
  FROM      produccion
  WHERE    (Lote_cliente = ''
     OR     Lote_cliente IS NULL)
    and     fecha <= '2018-04-19'
  GROUP BY  Nave, Producto, TAMANO, Espesor, Seleccion, Marcado, Formato, Particularidad, Codigo_referencia, Producto_tipo,Lote_cliente
  ORDER BY  Nave, Producto, TAMANO, Espesor, Seleccion, Marcado, Formato, Particularidad, Codigo_referencia, Producto_tipo,Lote_cliente
                 )  as p1
 
left outer join  (  SELECT      p.Nave,
              p.Producto,
              CONCAT(p.Longitud, 'x', p.Anchura) AS TAMANO,
              p.Espesor,
              p.Seleccion,
              p.Marcado,
              p.Formato,
              p.Particularidad,
              p.Codigo_referencia,
              Producto_tipo,
              SUM(p.Piezas)                      AS TOTALPIEZAS,
              COUNT(*)                           AS TOTALPALES
 
        FROM  lote_cliente as l
  INNER JOIN  produccion   as p
          ON  p.lote_cliente = l.numero_lote_cliente
       WHERE  l.fecha_preparacion <= '2018-04-19'
    GROUP BY  Nave, Producto, TAMANO, Espesor, Seleccion, Marcado, Formato, Particularidad, Codigo_referencia, Producto_tipo,Lote_cliente
    ORDER BY  Nave, Producto, TAMANO, Espesor, Seleccion, Marcado, Formato, Particularidad, Codigo_referencia, Producto_tipo,Lote_cliente
                 ) as p2
             on  p2.nave              = p1.nave
            and  p2.producto          = p1.producto
            and  p2.tamano            = p1.tamano
            and  p2.espesor           = p1.espesor
            and  p2.seleccion         = p1.seleccion
            and  p2.marcado           = p1.marcado
            and  p2.formato           = p1.formato
            and  p2.Producto_tipo     = p1.Producto_tipo
 
            and  p2.codigo_referencia = p1.codigo_referencia
 
       order by  p1.Nave, p1.Producto, p1.TAMANO, p1.Espesor, p1.Seleccion, p1.Marcado, p1.Formato, p1.Particularidad, p1.Codigo_referencia,p1.Producto_tipo

J'ai bien mes 26 palettes de 27x18.
Au 2018-04-20 j'ai 26 palettes.
Au 2018-04-21 j'ai 27 palettes.
Apparemment cela devrais être bon mais ce n'est pas le cas.
J'ai rajouter 18/test a une palette de 27x18 du 2018-01-11 et executer le code avec la date du 2018-01-30 comme ceci.

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
select           p1.*,
                 p2.*,
                 p1.totalPiezas - coalesce(p2.totalPiezas,0) as DiffPiezas,
                 p1.totalPales  - coalesce(p2.totalPales,0)  as DiffPales
           from  (  SELECT    Nave,
            Producto,
            CONCAT(Longitud, 'x', Anchura) AS TAMANO,
            Espesor,
            Seleccion,
            Marcado,
            Formato,
            Particularidad,
            Codigo_referencia,
            Producto_tipo,
            SUM(Piezas)                    AS TOTALPIEZAS,
            COUNT(*)                       AS TOTALPALES
  FROM      produccion
  WHERE    (Lote_cliente = ''
     OR     Lote_cliente IS NULL)
    and     fecha <= '2018-01-30'
  GROUP BY  Nave, Producto, TAMANO, Espesor, Seleccion, Marcado, Formato, Particularidad, Codigo_referencia, Producto_tipo,Lote_cliente
  ORDER BY  Nave, Producto, TAMANO, Espesor, Seleccion, Marcado, Formato, Particularidad, Codigo_referencia, Producto_tipo,Lote_cliente
                 )  as p1
 
left outer join  (  SELECT      p.Nave,
              p.Producto,
              CONCAT(p.Longitud, 'x', p.Anchura) AS TAMANO,
              p.Espesor,
              p.Seleccion,
              p.Marcado,
              p.Formato,
              p.Particularidad,
              p.Codigo_referencia,
              Producto_tipo,
              SUM(p.Piezas)                      AS TOTALPIEZAS,
              COUNT(*)                           AS TOTALPALES
 
        FROM  lote_cliente as l
  INNER JOIN  produccion   as p
          ON  p.lote_cliente = l.numero_lote_cliente
       WHERE  l.fecha_preparacion <= '2018-01-30'
    GROUP BY  Nave, Producto, TAMANO, Espesor, Seleccion, Marcado, Formato, Particularidad, Codigo_referencia, Producto_tipo,Lote_cliente
    ORDER BY  Nave, Producto, TAMANO, Espesor, Seleccion, Marcado, Formato, Particularidad, Codigo_referencia, Producto_tipo,Lote_cliente
                 ) as p2
             on  p2.nave              = p1.nave
            and  p2.producto          = p1.producto
            and  p2.tamano            = p1.tamano
            and  p2.espesor           = p1.espesor
            and  p2.seleccion         = p1.seleccion
            and  p2.marcado           = p1.marcado
            and  p2.formato           = p1.formato
            and  p2.Producto_tipo     = p1.Producto_tipo
 
            and  p2.codigo_referencia = p1.codigo_referencia
 
       order by  p1.Nave, p1.Producto, p1.TAMANO, p1.Espesor, p1.Seleccion, p1.Marcado, p1.Formato, p1.Particularidad, p1.Codigo_referencia,p1.Producto_tipo

Je devais avoir comme retour 14 palettes de 27x18 et non 13. Car le lot 18/test est du 2018-04-20.

**escartefigue** · 24/04/2018, 13h35

Bonjour,

Je n'ai pas lu tous les échanges qui précèdent, mais je remarque que le groupage et le tri des requêtes imbriquées sont incohérents avec le SELECT au niveau de la colonne Lote_cliente

De plus, les tris dans les requêtes imbriquées sont inutiles puisque vous faites un tri du résultat final

**superthx** · 24/04/2018, 14h39

Bonjour escartefigue,

Q'est ce qui va pas avec le SELECT au niveau de la colonne Lote_cliente ?

Soustraction entre 2 requetes / Erreur SQL (1241):Operand should contain 1 column(s)

Requêtes MySQL

Discussions similaires

Partager

Partager