Limitation de résultats

**Khelkundotre** · 07/06/2017, 16h21

Bonjour à tous

,

Dans ma base de données j'ai une table Produits et une tables Commandes.
Pour chacune de mes commandes sur un intervalle de temps (par exemple pour toutes les commandes réalisées dans la journée), je souhaiterais récupérer un historique de mes commandes plus ancienne pour chaque produit commandé dans l'intervalle de temps.

J'aimerais donc avoir un tableau ressemblant au suivant :

Produit	Date de la commande	Date de l'ancienne commande
14	20/05/2017	12/01/2015
14	20/05/2017	24/02/2011
25	15/05/2017	10/01/2016

Cependant je voudrais limiter le nombre de résultats par produit commandé pour ne pas avoir un historique trop long, car pour le moment avec ce que j'ai réalisé j'obtiens une ligne pour chaque commande passé pour chaque produit.

Par exemple si pour un produit on a 30 commandes dans notre table alors j'ai 30 lignes pour le produit alors que je n'en voudrait que 2 et avoir un tableau ressemblant à :

Produit	Date de la commande	Date de l'ancienne commande
14	20/05/2017	12/01/2015
14	20/05/2017	24/02/2011
25	15/05/2017	10/01/2016
25	15/05/2017	06/02/2011
36	12/12/2016	02/05/2014
36	12/12/2016	12/12/2012

Ne travaillant qu'avec du MySQL j'ai un peu du mal à voir comment réaliser cela

. Existerai-t-il un moyen de faire un compteur pour limiter le nombre de résultat par produit ou autre ?
Il s'agit d'un réel casse tête pour moi et je ne suis pas parvenu à trouver une solution en cherchant sur internet.

Si certains peuvent m'éclairer la voie ce serais fantastique.

Merci d'avance

,
Cordialement.

**Artemus24** · 07/06/2017, 19h52

Salut quelqu'un d'autre !

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
--------------
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 primary key,
  `nom`   varchar(255)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `produit` (`id`,`nom`) values
  (14, 'produit 14'),
  (25, 'produit 25'),
  (36, 'produit 36')
--------------
 
--------------
select * from `produit`
--------------
 
+----+------------+
| id | nom        |
+----+------------+
| 14 | produit 14 |
| 25 | produit 25 |
| 36 | produit 36 |
+----+------------+
--------------
DROP TABLE IF EXISTS `commande`
--------------
 
--------------
CREATE TABLE `commande`
( `id`           integer unsigned  NOT NULL auto_increment primary key,
  `date`         date              NOT NULL,
  `produit`      integer unsigned  NOT NULL,
  CONSTRAINT `FK_PRODUIT` FOREIGN KEY (`produit`) REFERENCES `produit` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `commande` (`date`,`produit`) values
  ('2010-01-03', 36),
  ('2010-05-08', 14),
  ('2011-01-25', 25),
  ('2011-02-06', 25),
  ('2011-02-24', 14),
  ('2012-12-12', 36),
  ('2014-05-06', 36),
  ('2015-01-12', 14),
  ('2016-01-10', 25),
  ('2016-12-12', 36),
  ('2017-05-15', 25),
  ('2017-05-20', 14)
--------------
 
--------------
select * from `commande`
--------------
 
+----+------------+---------+
| id | date       | produit |
+----+------------+---------+
|  1 | 2010-01-03 |      36 |
|  2 | 2010-05-08 |      14 |
|  3 | 2011-01-25 |      25 |
|  4 | 2011-02-06 |      25 |
|  5 | 2011-02-24 |      14 |
|  6 | 2012-12-12 |      36 |
|  7 | 2014-05-06 |      36 |
|  8 | 2015-01-12 |      14 |
|  9 | 2016-01-10 |      25 |
| 10 | 2016-12-12 |      36 |
| 11 | 2017-05-15 |      25 |
| 12 | 2017-05-20 |      14 |
+----+------------+---------+
--------------
drop view if exists `vue`
--------------
 
--------------
create view `vue` as
  select           t1.produit,
                   t2.nom,
                   t1.date as last_date,
                   t3.date as  old_date,
                   count(t4.id)  as rang
 
             from  commande as t1
 
       inner join  produit  as t2
               on  t2.id = t1.produit
 
       inner join  commande as t3
               on  t3.produit = t1.produit
 
  left outer join  commande as t4
               on  t4.produit = t1.produit
              and  t4.date > t3.date
 
            where  t1.date = ( select max(date) from commande as ta where ta.produit = t1.produit)
              and  t3.date < t1.date
 
         group by  t1.produit, t2.nom, t1.date, t3.date
         order by  t1.produit, t2.nom, t1.date, t3.date desc
--------------
 
--------------
select * from vue
--------------
 
+---------+------------+------------+------------+------+
| produit | nom        | last_date  | old_date   | rang |
+---------+------------+------------+------------+------+
|      14 | produit 14 | 2017-05-20 | 2015-01-12 |    1 |
|      14 | produit 14 | 2017-05-20 | 2011-02-24 |    2 |
|      14 | produit 14 | 2017-05-20 | 2010-05-08 |    3 |
|      25 | produit 25 | 2017-05-15 | 2016-01-10 |    1 |
|      25 | produit 25 | 2017-05-15 | 2011-02-06 |    2 |
|      25 | produit 25 | 2017-05-15 | 2011-01-25 |    3 |
|      36 | produit 36 | 2016-12-12 | 2014-05-06 |    1 |
|      36 | produit 36 | 2016-12-12 | 2012-12-12 |    2 |
|      36 | produit 36 | 2016-12-12 | 2010-01-03 |    3 |
+---------+------------+------------+------------+------+
--------------
select  produit,
        last_date as 'date de la commande',
        old_date  as 'date de l''ancienne commande'
  from  vue
 where  rang < 3
--------------
 
+---------+---------------------+-----------------------------+
| produit | date de la commande | date de l'ancienne commande |
+---------+---------------------+-----------------------------+
|      14 | 2017-05-20          | 2015-01-12                  |
|      14 | 2017-05-20          | 2011-02-24                  |
|      25 | 2017-05-15          | 2016-01-10                  |
|      25 | 2017-05-15          | 2011-02-06                  |
|      36 | 2016-12-12          | 2014-05-06                  |
|      36 | 2016-12-12          | 2012-12-12                  |
+---------+---------------------+-----------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

**al1_24** · 07/06/2017, 19h54

En supposant que tu ne cherches que la date de la commande :
Pour identifier la dernière commande, c'est la date la plus élevée de commande d'un produit :

Code :

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

1
2
3
4
5
SELECT  produit
	,   MAX(dt_commande)	AS der_cmd
FROM    matable
GROUP BY produit
;

Pour identifier la date de la commande précédente, c'est la date la plus élevée antérieure à la dernière commande du même produit :

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  tb1.produit
	,   tb1.der_cmd
	,   MAX(tb2.dt_commande)	AS prec_cmd
FROM    (	SELECT  produit
				,   MAX(dt_commande)	AS der_cmd
			FROM    matable
			GROUP BY produit
		)   AS  tb1
	LEFT JOIN
		matable tb2
		ON  tb1.produit = tb2.produit
		AND tb1.der_cmd > tb2.dt_commande
GROUP BY tb1.produit
;

A toi la suite et la mise en forme...

Avec un SGBD qui prend en charge les fonctions de regroupement fenêtrées, c'est extrêmement plus facile.

**Khelkundotre** · 08/06/2017, 09h28

Bonjour

,

Merci de votre aide à tous les deux !

La solution d'Artemus24 m'intéresse beaucoup car facilement modulable dans le cas où l'on veuille un jour changer le nombre de résultats que l'on désire.
En revanche j'ai oublié de préciser que les dates sont présentes dans une table séparée aux commandes et que mes commandes possèdent une référence à cette table

.
En transcrivant le code d'Artemus24 avec ma base de données je rencontre des difficultés lorsque j'en arrive à cette partie :

Code :

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

1
2
3
4
 
  left outer join  commande as t4
               on  t4.produit = t1.produit
              and  t4.date > t3.date

Donc je l'aurais bien fait avec t4.date_id > t3.date_id sauf que je peux avoir quelque chose comme cela dans ma table :

id_date	date
1	20/01/2017
2	30/08/2016
3	15/12/2016

Faudrait-il que je passe par une autre vue où je ferais la liaison entre la commande et la date pour avoir quelque chose ressemblant à :

Commande	Date commande
1	12/05/2016
2	25/12/2014

Ou existe-t-il une alternative plus simple ?

En tout cas merci de votre aide

Je commence à voir le bout du tunnel grâce à vous !

Bonne journée

**Artemus24** · 08/06/2017, 10h01

Salut Khelkundotre.

Merci pour le '-1'.

Envoyé par Khelkundotre

j'ai oublié de préciser que les dates sont présentes dans une table séparée aux commandes et que mes commandes possèdent une référence à cette table.

Et en quoi cela pose un problème ?

Envoyé par Khelkundotre

En transcrivant le code d'Artemus24 avec ma base de données je rencontre des difficultés lorsque j'en arrive à cette partie :

La jointure t4 permet de comptabiliser le nombre de lignes qui se fait ici :

Code :

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

count(t4.id) as rang

Chaque commande a un numéro d'identifiant différent.
Sauf que si je fais cela sur t3.id, j'aurai toujours le même nombre de lignes pour toutes les lignes sélectionnées pour un produit données.
Je suis obligé de donné un critère pour indiquer comme je veux comptabiliser mes lignes.

C'est le rôle de cette jointure, qui va trier dans l'ordre que je désire obtenir :

Code :

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

1
2
3
left outer join  commande as t4
             on  t4.produit = t1.produit
            and  t4.date > t3.date

Je fais un produit cartésien sur le critère du produit sélectionné.
Autrement dit, pour une ligne sélectionnée de la jointure t3, je sélectionne les lignes de la jointure t4 ayant la date plus grande strictement.
Cela va produire comme résultat un tri allant du plus récent au plus ancien.
Et de ce fait, le plus récent aura la plus petite valeur de rang et le plus ancien aura la plus grande valeur.

Envoyé par Khelkundotre

Donc je l'aurais bien fait avec t4.date_id > t3.date_id sauf que je peux avoir quelque chose comme cela dans ma table :

Vous voulez dire, trier sur la colonne "date_id".

Avez-vous testé en remplaçant mon critère par le votre ?

@+

**Khelkundotre** · 08/06/2017, 10h56

Il ne s'agit pas de moi pour le '-1'

Envoyé par Artemus24

Citation Envoyé par Khelkundotre

Envoyé par Khelkundotre

j'ai oublié de préciser que les dates sont présentes dans une table séparée aux commandes et que mes commandes possèdent une référence à cette table.

Et en quoi cela pose un problème ?

Le problème c'est que quand j'arrive à la partie suivante, dont j'ai très bien compris l'utilité et le fonctionnement, je ne vois pas comment réaliser la partie après le "AND" à cause de la date qui est stocké dans une autre table.

Code :

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

1
2
3
left outer join  commande as t4
             on  t4.produit = t1.produit
            and  t4.date > t3.date

Je ne peux pas le réaliser quelque chose ressemblant à

Code :

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

1
2
3
left outer join  commande as t4
             on  t4.produit = t1.produit
            and  t4.date_id > t3.date_id

car comme j'ai essayé de le montrer dans un tableau un id plus grand ne correspond pas forcément à une date plus récente ou plus ancienne.

id_date	date
1	20/01/2017
2	30/08/2016
3	15/12/2016

J'imagine qu'il faudrait réaliser quelque chose ressemblant à ça

Code :

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

1
2
3
4
5
6
7
8
9
inner join  commande as t3
             on  t3.produit = t1.produit
inner join dates as d1
             on d1.id_date=t3.id_date
left outer join  commande as t4
             on  t4.produit = t1.produit
            and  d1.date > d2.date
left outer join dates as d2
             on d1.id_date=t4.id_date

mais je ne vois pas du tout comment faire car j'aurais besoin de d2 pour définir la jointure entre t4 et t1 mais j'ai également besoin de t4 pour définir d2 il me semble.

EDIT : J'ai essayé de faire la jointure suivante :

Code :

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

LEFT JOIN commandes as cmde4 ON cmde4.produit_id=cmde1.produit_id AND cmde4.date_id>cmde3.date_id

Et cela m'a donné ce résultat pour un produit donné :

Produit	Date dernière commande	Date ancienne commande	Rang
441	2017-05-11	2017-05-10	4
441	2017-05-11	2017-03-16	0
441	2017-05-11	2017-03-13	4
441	2017-05-11	2016-11-14	5

Je ne comprends pas comment on peut trouver le rang 4 plusieurs fois alors que les dates ne sont pas identique.
/!\ Je rappelle que je ne veux pas faire la jointure sur l'id de la date et que cela est réalisé à but expérimental.

**Artemus24** · 08/06/2017, 11h27

Salut Khelkundotre.

Merci d'avoir ajouté un '+1' !

Selon votre premier message, j'avais compris que tout ce dont vous aviez besoin, se trouvait dans la table "commande".
Pouvez-vous me communiquer votre jeu d'essai représentatif de ce que vous avez dans votre base de données ?
A savoir :
--> table des commandes
--> table des dates
--> table des produits

Veuillez précisez sous la forme de tableau (cela me convient parfaitement), les colonnes utilisées et les liens entre elles.

Dois-je supposer que vous désirez obtenir les dates triées du plus recents au plus anciens ?

@+

**Khelkundotre** · 08/06/2017, 11h52

Re

Comme demandé voici une représentation des données (je ne met ici que les données nécessaires à la requête avec quelques colonnes supplémentaires ) avec un exemple.

Envoyé par Artemus24

Pouvez-vous me communiquer votre jeu d'essai représentatif de ce que vous avez dans votre base de données ?
A savoir :
--> table des commandes
--> table des dates
--> table des produits

Veuillez précisez sous la forme de tableau (cela me convient parfaitement), les colonnes utilisées et les liens entre elles.

Table commandes :

Id commande	Produit_id	Date_id
A1202535	0000014	12

Table dates :

Id_date	Dates
12	2017-06-08

Table produits :

Id_produit	Nom_produit
0000014	Objet super utile

Liens entre les tables :

Champs 1	Champs 2
commandes.produit_id	produits.id_produit
commandes.date_id	dates.id_date

Pour remettre un peu les choses au clair, ce qui m'irait très bien c'est une vue comme le proposait Armetus24 ressemblant à cela :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
+---------+------------+------------+------------+------+
| produit | nom        | last_date  | old_date   | rang |
+---------+------------+------------+------------+------+
|      14 | produit 14 | 2017-05-20 | 2015-01-12 |    1 |
|      14 | produit 14 | 2017-05-20 | 2011-02-24 |    2 |
|      14 | produit 14 | 2017-05-20 | 2010-05-08 |    3 |
|      25 | produit 25 | 2017-05-15 | 2016-01-10 |    1 |
|      25 | produit 25 | 2017-05-15 | 2011-02-06 |    2 |
|      25 | produit 25 | 2017-05-15 | 2011-01-25 |    3 |
|      36 | produit 36 | 2016-12-12 | 2014-05-06 |    1 |
|      36 | produit 36 | 2016-12-12 | 2012-12-12 |    2 |
|      36 | produit 36 | 2016-12-12 | 2010-01-03 |    3 |
+---------+------------+------------+------------+------+

Pour moi ce serai le top du top comme ça je n'ai plus qu'à faire une recherche sur cette vue par la suite.

Pour ceux qui n'aurais pas vu, j'ai réédité mon précédent message avec un petit test de ce qu'Artemus24 m'a proposé. Je vous remet l'edit ci-dessous.

Envoyé par Khelkundotre

EDIT : J'ai essayé de faire la jointure suivante :

Code :

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

LEFT JOIN commandes as cmde4 ON cmde4.produit_id=cmde1.produit_id AND cmde4.date_id>cmde3.date_id

Et cela m'a donné ce résultat pour un produit donné :

Produit	Date dernière commande	Date ancienne commande	Rang
441	2017-05-11	2017-05-10	4
441	2017-05-11	2017-03-16	0
441	2017-05-11	2017-03-13	4
441	2017-05-11	2016-11-14	5

Je ne comprends pas comment on peut trouver le rang 4 plusieurs fois alors que les dates ne sont pas identique.
/!\ Je rappelle que je ne veux pas faire la jointure sur l'id de la date et que cela est réalisé à but expérimental.

EDIT : En y repensant, comment est-ce possible d'obtenir 0 dans le cas où la commande que l'on regarde n'est pas la commande la plus récente pour ce produit ?

Je vous met ici mon code (possible que j'ai fait une erreur que je n'aurais pas vu) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
DROP VIEW IF EXISTS historique;
CREATE VIEW historique AS
SELECT cmde1.produit_id, d1.date as derniere_date, d2.date as date_cmde, count(cmde4.id_commande) as rang
FROM commandes as cmde1
INNER JOIN dates as d1 ON d1.id_date=cmde1.date_id
INNER JOIN commandes as cmde3 ON cmde3.produit_id=cmde1.produit_id
INNER JOIN dates as d2 ON d2.id_date=cmde3.date_id
LEFT JOIN commandes as cmde4 ON cmde4.produit_id=cmde1.produit_id AND cmde4.date_id>cmde3.date_id
WHERE d1.date=(SELECT MAX(date) FROM commandes as ca LEFT JOIN dates d ON d.id_date=ca.date_id WHERE ca.produit_id=cmde1.produit_id)
AND d2.date<d1.date
GROUP BY cmde1.produit_id, d1.date, d2.date
ORDER BY produit_id, d1.date, d2.date DESC;

**Artemus24** · 08/06/2017, 12h57

Salut Khelkundotre.

J'ai repris le même jeu d'essai, en ajoutant votre table des dates.
A vous de le tester et de me dire ce qui ne va pas !

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
--------------
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 primary key,
  `nom`   varchar(255)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `produit` (`id`,`nom`) values
  (14, 'produit 14'),
  (25, 'produit 25'),
  (36, 'produit 36')
--------------
 
--------------
select * from `produit`
--------------
 
+----+------------+
| id | nom        |
+----+------------+
| 14 | produit 14 |
| 25 | produit 25 |
| 36 | produit 36 |
+----+------------+
--------------
DROP TABLE IF EXISTS `date`
--------------
 
--------------
CREATE TABLE `date`
( `id`           integer unsigned  NOT NULL auto_increment primary key,
  `date`         date              NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `date` (`date`) values
  ('2010-01-03'),  ('2010-05-08'),  ('2011-01-25'),  ('2011-02-06'),  ('2011-02-24'),  ('2012-12-12'),
  ('2014-05-06'),  ('2015-01-12'),  ('2016-01-10'),  ('2016-12-12'),  ('2017-05-15'),  ('2017-05-20')
--------------
 
--------------
select * from `date`
--------------
 
+----+------------+
| id | date       |
+----+------------+
|  1 | 2010-01-03 |
|  2 | 2010-05-08 |
|  3 | 2011-01-25 |
|  4 | 2011-02-06 |
|  5 | 2011-02-24 |
|  6 | 2012-12-12 |
|  7 | 2014-05-06 |
|  8 | 2015-01-12 |
|  9 | 2016-01-10 |
| 10 | 2016-12-12 |
| 11 | 2017-05-15 |
| 12 | 2017-05-20 |
+----+------------+
--------------
DROP TABLE IF EXISTS `commande`
--------------
 
--------------
CREATE TABLE `commande`
( `id_commande`   integer unsigned  NOT NULL auto_increment primary key,
  `id_date`       integer unsigned  NOT NULL,
  `id_produit`    integer unsigned  NOT NULL,
  CONSTRAINT `FK_DATE`    FOREIGN KEY (`id_date`)    REFERENCES `date`    (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_PRODUIT` FOREIGN KEY (`id_produit`) REFERENCES `produit` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `commande` (`id_date`,`id_produit`) values
  (01, 36),  (02, 14),  (03, 25),  (04, 25),  (05, 36),  (06, 14),
  (07, 14),  (08, 25),  (09, 36),  (10, 36),  (11, 25),  (12, 14)
--------------
 
--------------
select * from `commande`
--------------
 
+-------------+---------+------------+
| id_commande | id_date | id_produit |
+-------------+---------+------------+
|           1 |       1 |         36 |
|           2 |       2 |         14 |
|           3 |       3 |         25 |
|           4 |       4 |         25 |
|           5 |       5 |         36 |
|           6 |       6 |         14 |
|           7 |       7 |         14 |
|           8 |       8 |         25 |
|           9 |       9 |         36 |
|          10 |      10 |         36 |
|          11 |      11 |         25 |
|          12 |      12 |         14 |
+-------------+---------+------------+
--------------
drop view if exists `vue`
--------------
 
--------------
create view `vue` as
  select           t1.id_produit as produit,
                   t2.nom,
                   t3.date       as last_date,
                   t5.date       as old_date,
                   count(t7.id)  as rang
 
             from  `commande`    as t1
 
       inner join  `produit`     as t2
               on  t2.id         =  t1.id_produit
 
       inner join  `date`        as t3
               on  t3.id         =  t1.id_date
 
       inner join  `commande`    as t4
               on  t4.id_produit =  t1.id_produit
 
       inner join  `date`        as t5
               on  t5.id         =  t4.id_date
              and  t5.date       <  t3.date
 
  left outer join  `commande`    as t6
               on  t6.id_produit =  t1.id_produit
 
  left outer join  `date`        as t7
               on  t7.id         =  t6.id_date
              and  t7.date       >  t5.date
 
            where  t3.date = ( select  max(tb.date)
                                 from  `commande`    as ta
                           inner join  `date`        as tb
                                   on  tb.id         = ta.id_date
                                where  ta.id_produit = t1.id_produit )
 
         group by  t1.id_produit, t2.nom, t3.date, t5.date
         order by  t1.id_produit, t2.nom, t3.date, t5.date desc
--------------
 
--------------
select * from vue
--------------
 
+---------+------------+------------+------------+------+
| produit | nom        | last_date  | old_date   | rang |
+---------+------------+------------+------------+------+
|      14 | produit 14 | 2017-05-20 | 2014-05-06 |    1 |
|      14 | produit 14 | 2017-05-20 | 2012-12-12 |    2 |
|      14 | produit 14 | 2017-05-20 | 2010-05-08 |    3 |
|      25 | produit 25 | 2017-05-15 | 2015-01-12 |    1 |
|      25 | produit 25 | 2017-05-15 | 2011-02-06 |    2 |
|      25 | produit 25 | 2017-05-15 | 2011-01-25 |    3 |
|      36 | produit 36 | 2016-12-12 | 2016-01-10 |    1 |
|      36 | produit 36 | 2016-12-12 | 2011-02-24 |    2 |
|      36 | produit 36 | 2016-12-12 | 2010-01-03 |    3 |
+---------+------------+------------+------------+------+
--------------
select  produit,
        last_date as 'date de la commande',
        old_date  as 'date de l''ancienne commande'
  from  vue
 where  rang < 3
--------------
 
+---------+---------------------+-----------------------------+
| produit | date de la commande | date de l'ancienne commande |
+---------+---------------------+-----------------------------+
|      14 | 2017-05-20          | 2014-05-06                  |
|      14 | 2017-05-20          | 2012-12-12                  |
|      25 | 2017-05-15          | 2015-01-12                  |
|      25 | 2017-05-15          | 2011-02-06                  |
|      36 | 2016-12-12          | 2016-01-10                  |
|      36 | 2016-12-12          | 2011-02-24                  |
+---------+---------------------+-----------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

**Khelkundotre** · 08/06/2017, 15h27

Re,

Je viens donc d'essayer et cela fonctionne très bien ! Sauf dans un cas...

Dans le cas où on a un produit qui est commandé plusieurs fois dans la même journée, ce qui malheureusement peut arriver (et arrive assez souvent dans mon cas).

Produit	Old_date	Rang
1	03/06/2017	1
1	24/01/2016	2
1	24/01/2016	2
2	06/01/2017	1

Donc dans le cas où je voudrais limiter mon nombre de résultat à 2 anciennes commandes par produit alors cela ne fonctionne plus en faisant la requête suivante et me retournerait 3 résultats pour le produit 1 :

Code :

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

1
2
3
select * 
from  vue
where  rang < 3

J'ai essayé de regarder en passant également par l'id_commande et autres champs mais je ne vois pas comment y parvenir

Si cela peut aider je met une idée de la vue que je veux obtenir avec tous les champs.

Produit

Quantité

Prix_U

Autre Prix_U

Fournisseur

Date ancienne commande

Rang

Le rang étant le champ calculé qui nous permettra ensuite de choisir nos anciennes commandes.
Il est possible d'avoir pour seule différence entre les lignes juste le rang.
En reprenant l'exemple de tout à l'heure il me faudrait des résultats comme les suivants :

Produit	Old_date	Rang
1	03/06/2017	1
1	24/01/2016	2
1	24/01/2016	3
2	06/01/2017	1

En espérant que certains aient des idées...
En tout cas merci beaucoup Artemus pour l'aide précieuse que vous m'avez apporté !

**Artemus24** · 08/06/2017, 17h50

Salut Khelkundotre.

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
--------------
drop view if exists `vue`
--------------
 
--------------
create view `vue` as
  select           t1.id_produit  as produit,
                   t2.nom,
                   t3.date        as last_date,
                   t5.date        as old_date,
                   count(t7.id)   as rang
 
             from  `commande`     as t1
 
       inner join  `produit`      as t2
               on  t2.id          =  t1.id_produit
 
       inner join  `date`         as t3
               on  t3.id          =  t1.id_date
 
       inner join  `commande`     as t4
               on  t4.id_produit  =  t1.id_produit
 
       inner join  `date`         as t5
               on  t5.id          =  t4.id_date
              and  t5.date        <  t3.date
 
  left outer join  `commande`     as t6
               on  t6.id_produit  =  t1.id_produit
 
  left outer join  `date`         as t7
               on  t7.id          =  t6.id_date
              and  concat(t7.date,t6.id_commande) > concat(t5.date,t4.id_commande)
 
            where  t3.date = ( select  max(tb.date)
                                 from  `commande`    as ta
                           inner join  `date`        as tb
                                   on  tb.id         = ta.id_date
                                where  ta.id_produit = t1.id_produit )
 
         group by  t1.id_produit, t2.nom, t3.date, t5.date     , t4.id_commande
         order by  t1.id_produit, t2.nom, t3.date, t5.date desc
--------------
 
--------------
select * from vue
--------------
 
+---------+------------+------------+------------+------+
| produit | nom        | last_date  | old_date   | rang |
+---------+------------+------------+------------+------+
|      14 | produit 14 | 2016-12-12 | 2016-05-28 |    1 |
|      14 | produit 14 | 2016-12-12 | 2012-08-24 |    2 |
|      14 | produit 14 | 2016-12-12 | 2011-02-24 |    3 |
|      14 | produit 14 | 2016-12-12 | 2010-05-08 |    4 |
|      25 | produit 25 | 2017-05-15 | 2016-01-10 |    1 |
|      25 | produit 25 | 2017-05-15 | 2013-12-12 |    2 |
|      25 | produit 25 | 2017-05-15 | 2011-01-25 |    3 |
|      25 | produit 25 | 2017-05-15 | 2010-09-15 |    4 |
|      36 | produit 36 | 2017-05-20 | 2014-05-06 |    1 |
|      36 | produit 36 | 2017-05-20 | 2014-05-06 |    2 |
|      36 | produit 36 | 2017-05-20 | 2011-02-06 |    3 |
|      36 | produit 36 | 2017-05-20 | 2010-01-03 |    4 |
+---------+------------+------------+------------+------+
--------------
select    produit,
          last_date as 'date de la commande',
          old_date  as 'date de l''ancienne commande'
    from  vue
   where  rang < 3
order by  produit, rang
--------------
 
+---------+---------------------+-----------------------------+
| produit | date de la commande | date de l'ancienne commande |
+---------+---------------------+-----------------------------+
|      14 | 2016-12-12          | 2016-05-28                  |
|      14 | 2016-12-12          | 2012-08-24                  |
|      25 | 2017-05-15          | 2016-01-10                  |
|      25 | 2017-05-15          | 2013-12-12                  |
|      36 | 2017-05-20          | 2014-05-06                  |
|      36 | 2017-05-20          | 2014-05-06                  |
+---------+---------------------+-----------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

**Khelkundotre** · 09/06/2017, 12h25

Bonjour,

On y est presque !
Il y a juste une erreur que je n'arrive pas a comprendre.

Donc la requête fonctionne très bien on a bien un rang différent pour chaque commande lorsqu'elles sont commandées à la même date dans le cas où on est dans des commandes différentes.
En revanche je suis tombé sur des cas plutôt... mystérieux ça doit être le bon mot.

Voici l'exemple d'un cas qui fonctionne très bien :

Produit	Commande	Date_commande	Rang
1	A	2017-03-20	1
1	B	2017-03-20	2
1	C	2017-03-16	3
1	D	2016-11-14	4
1	E	2016-08-30	5
1	E	2016-08-30	5

Donc ça c'est super c'est ce qu'on voudrait

. En revanche voici l'exemple du cas plus mystérieux que j'ai réussi à obtenir :

Produit	Commande	Date_commande	Rang
2	F	2017-01-31	9
2	G	2016-09-01	12
2	G	2016-09-01	12
2	G	2016-09-01	12

Le rang est un peu parti dans tous les sens pour ce produit mais je ne vois pas du tout d'où cela peut venir car même dans ma table commandes je n'ai pas 9 enregistrements pour ce produit.

Je met un petit comparatif de ce que j'ai dans ma vue et ce que j'ai dans ma base de données :

VUE

TABLE COMMANDES

Produit	Commande	Date_commande	Rang
1	A	2017-03-20	1
1	B	2017-03-20	2
1	C	2017-03-16	3
1	D	2016-11-14	4
1	E	2016-08-30	5
1	E	2016-08-30	5

Produit	Commande	Date_commande
1	A	2017-03-20
1	B	2017-03-20
1	C	2017-03-16
1	D	2016-11-14
1	E	2016-08-30
1	E	2016-08-30
1	F	2017-06-08

Produit	Commande	Date_commande	Rang
2	F	2017-01-31	9
2	G	2016-09-01	12
2	G	2016-09-01	12
2	G	2016-09-01	12

Produit	Commande	Date_commande
2	F
2	F	2017-04-10
2	F	2017-04-10
2	G	2017-04-10
2	G	2017-01-31
2	G	2017-01-31
2	G	2017-01-31

En passant (beaucoup) de temps a travailler sur ces mystérieux résultats j'ai peut-être trouvé une piste.
Dans notre requête on a 3 fois la table commandes et dans la table commandes on a 3 fois le produit 2 pour la commande F et 4 fois le produit 2 pour la commande G
Or 3*4=12 et 3*3=9 donc je pense que cela vient de la jointure. En ajoutant une clause dans mon group by j'avais pu obtenir 21 pour le rang de chacune de mes lignes du produit 2 et dans ma table commandes j'ai 7 enregistrements pour ce produit (3*7=21).

J'ai également trouver quelque chose de similaire à ce que réalisait Artemus sur cette page mais avait des erreurs identiques à celles que je viens de décrire.

J'ai un peu du mal a saisir toute la subtilité de cette partie du code alors si je pourrais avoir quelques explications sur son fonctionnement ce serai gentil :

Code :

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

1
2
3
4
5
6
7
8
count(d3.id_date)  as rang
 
left outer join  `commandes`    as cmde3
               on  cmde3.produit_id =  cmde1.produit_id
 
  left outer join  `dates`        as d3
               on  d3.id_date         =  cmde3.date_id
              and  concat(d3.date, cmde3.cmde_four)       >  concat(d2.date,cmde2.cmde_four)

Merci pour toute l'aide déjà apportée.

Bon week end !

**Artemus24** · 09/06/2017, 13h15

Salut Khelkundotre.

Quel est le problème ?

1) vous désirez obtenir une View afin de pouvoir la réutiliser comme bon vous semble.
Donc, on ne peut pas utiliser les user_defined_variables car cela n'est pas compatible avec les View.
Si vous ne savez pas ce que c'est, ce sont les exemples donnés dans votre lien, là où il y a des variables commençant par "@".

2) MySql n'a pas encore intégré les rank() et dense_rank() comme sous Microsoft SQL Server.
De ce fait, il faut les simuler !

3) je suis parti de l'hypothèse que la "date" était incluse dans la table des "commandes".
Vous m'avez répondu que la date était externalisée dans une autre table. De cela, j'ai dû dédoubler les jointures.
Le problème ne vient pas de là, mais cela alourdi la requête de mise en forme.

4) pour numéroter les lignes, j'ai besoin d'avoir un critère qui soit unique. Et c'est là que vous avez un problème !!!
Je suis parti de l'hypothèse que chaque commande avait un numéro unique. Or ce n'est pas le cas d'après votre dernier exemple.

Dans le premier tableau, vous dites : "Voici l'exemple d'un cas qui fonctionne très bien :". Or ce n'est pas le cas !
Pour la première et deuxième ligne, vous avez des dates identiques mais avec deux numéros de commandes différents.
Il y a unicité et donc le calcul du rang va se faire sans problème.

Pour la cinquième et sixième ligne, vous avez encore des dates identiques, mais cette fois-ci deux numéros de commandes identiques.
Si vous avez juste deux lignes, ces deux lignes auront le même rang (ici 5), et la ligne suivante aura nécessairement le rang 5 incrémenté du nombre de lignes identiques, donc 2, ce qui donne 5+2=7.

5) pour résoudre ce problème, il faut trouver le moyen de rendre votre ligne de commande (je parle du tableau) unique.
Donc soit vous avez un problème de modélisation, soit vous ne m'avez pas tout dit sur le contenu de vos tables.
Une commande peut très bien se subdiviser en plusieurs lignes. Et donc numéro de commande + numéro de la ligne dans la commande pourrait résoudre ce problème.

6) c'est pourquoi, simplifier à outrance un problème que vous rencontrez, n'est pas la bonne pratique pour trouver la solution.
Comme vous l'avez constaté, à chaque fois, il y a un manque d'information dans vos exemples qui font que la recherche de la solution se complexifie.

@+

**Khelkundotre** · 12/06/2017, 18h48

Bonjour,

C'est bon j'ai réussi a faire ce que je voulais faire

J'ai du faire une vue alternative qui regroupe chaque produit pour chaque commande et ainsi faire le calcul de ligne sur cette vue au lieu de le faire directement sur mes tables.
Merci beaucoup pour l aide et les points expliqués, c'est beaucoup plus clair dans ma tête.

Limitation de résultats [MySQL-5.5]

Requêtes MySQL

Discussions similaires

Partager

Partager