Multiples "join" avec comparaison et agrégation

**onlajoy** · 25/01/2016, 14h55

Bonjour,

Je me tourne vers vous car malgré quelques heures passées je ne parviens pas à réaliser la requête qui fera mon bonheur.
J'ai simplifié mon problème, pourriez-vous m'aider.

Prenons les 3 tables suivantes :

produit	controle	complement
id : int PK nom : string	id : int PK produit_id : int FK couleur : string date : int (timestamp UNIX) stock : float	id : int PK produit_id : int FK couleur : string date : int (timestamp UNIX) ajout : float

J'ai à une date donnée, un produit donné, d'une couleur donnée, en une certaine quantité.
Cette valeur peut faire l'objet d'un contrôle qui renseigne une ligne dans la table contrôle
Il est possible d'ajouter du produit, ceci est tracé dans la table complément SAUF (si le processus est respecté) si un produit d'une couleur donnée arrive en rupture de stock lors d'un contrôle (c'est en ce cas non un complément mais un nouveau contrôle "évolué" qui doit être appliqué)

Je cherche à mettre en évidence 2 listes : 2 requêtes
1/ les produit en stock : le dernier contrôle (avec sa date) des stock différents de 0. => ça j'ai réussi : OK
2/ la liste cas hors processus : si le dernier contrôle réalisé pour un produit d'une couleur est à 0 et qu'il y a eu des ajouts depuis ce contrôle à 0. (avec en bonus la somme des quantité ajoutées) => là, j'avoue que je n'y arrive pas.

J'ai fait ceci pour le cas n°1 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT a.* , produit.nom
FROM controle a
INNER JOIN (
	SELECT id, MAX(date) date
	FROM controle
	WHERE stock <> 0 
		AND stock is not null
	GROUP BY produit_id, couleur
) b ON a.id = b.id
LEFT JOIN produit ON a.produit_id = utilisateur.id
ORDER BY a.produit_id, a.couleur

Et j'ai essayé plusieurs choses pour le cas n°2 sans succès

Ma dernière tentative étant celle-ci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT a.* , produit.nom, SUM(complement.ajout)
FROM controle a
INNER JOIN (
	SELECT id, MAX(date) date
	FROM controle
	WHERE stock <> 0 
		AND stock is not null
	GROUP BY produit_id, couleur
) b ON a.id = b.id
LEFT JOIN produit ON a.produit_id = utilisateur.id
INNER JOIN complement ON complement.produit_id = a.produit_id AND complement.couleur = a.couleur
WHERE complement.date > a.date
GROUP BY complement.produit_id, complement.couleur
ORDER BY a.produit_id, a.couleur

**escartefigue** · 25/01/2016, 16h35

remplacez la jointure inner vers votre table complément par une jointure outer et utilisez coalesce pour le complément, et min ou max pour le stock :

Code :

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

1
2
SELECT a.col1, a.col2..., produit.nom, coalesce(SUM(complement.ajout), max(produit.stock)) 
Group by  a.col1, a.col2..., produit.nom

**Artemus24** · 26/01/2016, 04h26

Salut onlajoy.

J'ai refais un jeu d'essai pour tester vos deux requêtes.
Ce qui serait bien, c'est de nous donner votre jeu d'essai et 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
--------------
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 `produit`
--------------
 
--------------
CREATE TABLE `produit`
( `id`    integer unsigned  NOT NULL AUTO_INCREMENT,
  `nom`   char(01)          NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `produit` (`nom`) values
  ('A'), ('B'), ('C'), ('D')
--------------
 
--------------
select * from produit
--------------
 
+----+-----+
| id | nom |
+----+-----+
|  1 | A   |
|  2 | B   |
|  3 | C   |
|  4 | D   |
+----+-----+
--------------
drop table if exists `controle`
--------------
 
--------------
CREATE TABLE `controle`
( `id`            integer unsigned  NOT NULL AUTO_INCREMENT,
  `produit_id`    integer unsigned  NOT NULL,
  `couleur`       char(10)          NOT NULL,
  `date`          date              NOT NULL,
  `stock`         decimal (10, 3)   NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_controle_produit` FOREIGN KEY (`produit_id`) REFERENCES `produit` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `controle` (`produit_id`,`couleur`,`date`,`stock`) values
  (2, 'rouge', '2015-12-31', 500.0),
  (2, 'rouge', '2015-11-30', 750.0),
  (2, 'vert',  '2015-12-31', 250.0),
  (2, 'vert',  '2015-11-30', 500.0),
  (3, 'rouge', '2015-12-31', 400.0),
  (3, 'rouge', '2015-11-30', 600.0),
  (3, 'vert',  '2015-12-31', 300.0),
  (3, 'vert',  '2015-11-30', 450.0),
  (4, 'rouge', '2015-12-31',   0.0),
  (4, 'rouge', '2015-11-30',  50.0),
  (4, 'vert',  '2015-12-31', 100.0),
  (4, 'vert',  '2015-11-30', 200.0)
--------------
 
--------------
select * from controle
--------------
 
+----+------------+---------+------------+---------+
| id | produit_id | couleur | date       | stock   |
+----+------------+---------+------------+---------+
|  1 |          2 | rouge   | 2015-12-31 | 500.000 |
|  2 |          2 | rouge   | 2015-11-30 | 750.000 |
|  3 |          2 | vert    | 2015-12-31 | 250.000 |
|  4 |          2 | vert    | 2015-11-30 | 500.000 |
|  5 |          3 | rouge   | 2015-12-31 | 400.000 |
|  6 |          3 | rouge   | 2015-11-30 | 600.000 |
|  7 |          3 | vert    | 2015-12-31 | 300.000 |
|  8 |          3 | vert    | 2015-11-30 | 450.000 |
|  9 |          4 | rouge   | 2015-12-31 |   0.000 |
| 10 |          4 | rouge   | 2015-11-30 |  50.000 |
| 11 |          4 | vert    | 2015-12-31 | 100.000 |
| 12 |          4 | vert    | 2015-11-30 | 200.000 |
+----+------------+---------+------------+---------+
--------------
drop table if exists `complement`
--------------
 
--------------
CREATE TABLE `complement`
( `id`            integer unsigned  NOT NULL AUTO_INCREMENT,
  `produit_id`    integer unsigned  NOT NULL,
  `couleur`       char(10)          NOT NULL,
  `date`          date              NOT NULL,
  `ajout`         decimal (10, 3)   NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk2_controle_produit` FOREIGN KEY (`produit_id`) REFERENCES `produit` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `complement` (`produit_id`,`couleur`,`date`,`ajout`) values
  (4, 'rouge', '2016-01-15', 100.0),
  (4, 'rouge', '2016-01-16',  50.0),
  (4, 'vert',  '2016-01-05',  50.0),
  (4, 'vert',  '2016-01-09',  25.0)
--------------
 
--------------
select * from complement
--------------
 
+----+------------+---------+------------+---------+
| id | produit_id | couleur | date       | ajout   |
+----+------------+---------+------------+---------+
|  1 |          4 | rouge   | 2016-01-15 | 100.000 |
|  2 |          4 | rouge   | 2016-01-16 |  50.000 |
|  3 |          4 | vert    | 2016-01-05 |  50.000 |
|  4 |          4 | vert    | 2016-01-09 |  25.000 |
+----+------------+---------+------------+---------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

Et voici les deux requêtes :

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
--------------
select tb2.nom,
       tb1.couleur,
       tb1.date,
       tb1.stock
from       controle as tb1
inner join produit  as tb2
on    tb2.id = tb1.produit_id
 
where tb1.date = ( select max(date) as date
                   from     controle as tb3
                   where    tb3.produit_id  = tb1.produit_id
                   and      tb3.couleur     = tb1.couleur
                   group by tb3.produit_id,   tb3.couleur
                                 )
order by tb2.nom, tb1.couleur
--------------
 
+-----+---------+------------+---------+
| nom | couleur | date       | stock   |
+-----+---------+------------+---------+
| B   | rouge   | 2015-12-31 | 500.000 |
| B   | vert    | 2015-12-31 | 250.000 |
| C   | rouge   | 2015-12-31 | 400.000 |
| C   | vert    | 2015-12-31 | 300.000 |
| D   | rouge   | 2015-12-31 |   0.000 |
| D   | vert    | 2015-12-31 | 100.000 |
+-----+---------+------------+---------+
--------------
select tb3.nom,
       tb1.couleur,
       sum(tb2.ajout) as ajout
from            controle   as tb1
 
left outer join complement as tb2
on  tb2.produit_id = tb1.produit_id
and tb2.couleur    = tb1.couleur
 
inner join      produit    as tb3
on tb3.id = tb1.produit_id
 
where tb1.date = ( select max(date) as date
                   from     controle as tb4
                   where  ( tb4.stock       = 0
                                   or       tb4.stock       is null )
                   and      tb4.produit_id  = tb1.produit_id
                   and      tb4.couleur     = tb1.couleur
                   group by tb4.produit_id,   tb4.couleur
                                 )
and tb2.date > tb1.date
 
group by tb3.nom, tb1.couleur
--------------
 
+-----+---------+---------+
| nom | couleur | ajout   |
+-----+---------+---------+
| D   | rouge   | 150.000 |
+-----+---------+---------+
 
Appuyez sur une touche pour continuer...

@+

**onlajoy** · 26/01/2016, 20h06

Bonjour à tous.

Tout d'abord merci de votre implication.
Comme je l'avais dit il ne s'agissait que d'un modèle "simplifié", pour mettre en évidence mon problème.

Et comme conseillé par Artemus24, j'ai cherché à refaire mon jeu de données simplifiées pour connaître exactement l'attendu...
Et là j'ai compris pourquoi ma seconde requête n'apportait aucun résultat : mon jeu de données était erroné.

Avant la date du contrôle, en base, la couleur était écrite "bleu_foncé" après la date du contrôle, la couleur était écrite "bleu_foncé".
Et comme je visualisait via l'interface html de l'application.....

Je me suis cassé les dents sur une requête fonctionnelle, mais un jeu de test erroné

Par contre, merci à escartefigue qui m'aura fait découvrir une fonction que le ne connaissais pas, je prends note cela m'évitera de poser la question quand je tomberai sur un cas nécessitant son utilisation.

Sur ce, bonne soirée à tous.

Cordialement.
Onlajoy

Multiples "join" avec comparaison et agrégation

Requêtes MySQL

Partager

Partager