Tranformer 2 requêtes/traitements en un

**Howely** · 21/01/2017, 23h40

Bonjour à tous

Voici mon problème:

Actuellement, un script php récupère par mysql une date dans une table.
Puis pour chaque ligne récupérée, une autre requête est exécutée sur une autre table pour récupérer une date si des conditions sont vérifiées (avec en plus un order desc limit 0,1).

Si j'ai ces 2 dates, je calcule la différence de jours entre elles que je cumule pour chaque ligne

ce qui donne en gros:

Code :

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

1
2
3
4
5
select Id,DateTransfere from TableBase where xxxxxxxxxx
boucle {
   select DateAction from TableTest where Idx=TableBase.Id and xxxxxxx order by Idx desc limit 0,1
   Si trouvé alors récupère DateAction  + traitement php
}

Comme c'est loin d'être optimisé pour un grand nombre de résultats, je voulais faire ce traitement en une seule requête sauf que......
j'ai testé avec inner join, exists etc mais rien n'y fais, il y a toujours quelque chose qui ne va pas

Je n'ai que le début, select SUM(DATEDIFF(DateAction ,DateTransfere )) ahah...

Auriez-vous une idée ou une piste? Vous l'aurez compris, je ne suis pas pro de sql

Merci d'avoir pris la peine de me lire en espérant avoir été clair...

**Artemus24** · 22/01/2017, 00h35

Salut howely.

Il nous faut le descriptif de tes tables + un jeu d'essai représentatif de ce que vous désirez faire, et bien sûr le résultat attendu !

@+

**Howely** · 22/01/2017, 01h29

hello!

d'abord merci d'y jeter un coup d'oeil

alors la 1ere table avec contenu:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE IF NOT EXISTS `tablebase` (
  `id` int(11) NOT NULL,
  `datebase` date NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
--
-- Contenu de la table `tablebase`
--
 
INSERT INTO `tablebase` (`id`, `date`) VALUES
(1, '2017-01-01'),
(2, '2017-01-02'),
(3, '2017-01-04');

et la 2e avec contenu

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
CREATE TABLE IF NOT EXISTS `tabletest` (
  `idx` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  `datetest` date NOT NULL,
  `texte` varchar(10) NOT NULL,
  UNIQUE KEY `idx` (`idx`),
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
--
-- Contenu de la table `tabletest`
--
 
INSERT INTO `tabletest` (`idx`, `id`, `date`, `texte`) VALUES
(1, 1, '2017-01-18', 'lol'),
(2, 1, '2017-01-18', 'vrai'),
(3, 1, '2017-01-27', 'faux'),
(4, 2, '2017-01-07', 'faux'),
(5, 2, '2017-01-20', 'vrai'),
(6, 3, '2017-01-19', 'hein'),
(7, 3, '2017-01-11', 'faux'),
(8, 3, '2017-01-15', 'vrai'),
(9, 3, '2017-01-14', 'vrai'),
(10, 1, '2017-01-28', 'hein');

Donc l'idée:
Pour chaque ligne de tablebase, on récupère la date

select datebase from tablebase

Pour chaque ligne de tablebase, on interroge la tabletest (liée à tablebase sur id) en ne prenant que la dernière entrée

Exemple: select datetest from tabletest where id=1 order by idx desc limit 1

Sur cette requête, on regarde si le texte de la dernière entrée est "vrai"
Si ce n'est pas "vrai" en contenu, on passe à la ligne suivante de tablebase
Si c'est bien "vrai", on prend la datetest et on récupère la différence de jours entre datetest et datebase
On passe ensuite à la ligne de tablebase suivante et on repète ça à chaque fois, en sachant qu'on additionne la différence que l'on trouve (d'où le SUM de mon premier post)

Donc si on suit les données d'exemple:

les id 2 et 3 de tablebase ont pour données de la dernière ligne (order idx desc) de la tabletest "vrai"
on récupère donc par exemple pour id 2 la datebase 2017-01-02 et la datetest 2017-01-20
le datediff donne donc 18, qu'on additionnera avec ce qu'on trouvera sur l'id 3, soit 10 jours: à la fin on doit retrouver 28 jours de différence en tout

Voilà j'espère avoir été plus clair et que la solution ne saute pas aux yeux (j'aurai honte :p)

**Artemus24** · 22/01/2017, 20h13

Salut Howely.

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET collation_connection = latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `tablebase`
--------------
 
--------------
CREATE TABLE `tablebase`
( `id`        integer unsigned  not null auto_increment primary key,
  `datebase`  date              not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tablebase` (`datebase`) values
  ('2017-01-01'),('2017-01-02'),('2017-01-04')
--------------
 
--------------
select * from tablebase
--------------
 
+----+------------+
| id | datebase   |
+----+------------+
|  1 | 2017-01-01 |
|  2 | 2017-01-02 |
|  3 | 2017-01-04 |
+----+------------+
--------------
DROP TABLE IF EXISTS `tabletest`
--------------
 
--------------
CREATE TABLE `tabletest`
( `id`        integer unsigned  not null auto_increment primary key,
  `clef`      integer unsigned  not null,
  `datetest`  date              not null,
  `texte`     char(04)          not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `tabletest` (`clef`, `datetest`, `texte`) VALUES
  (1, '2017-01-18', 'lol'),
  (1, '2017-01-18', 'vrai'),
  (1, '2017-01-27', 'faux'),
  (2, '2017-01-07', 'faux'),
  (2, '2017-01-20', 'vrai'),
  (3, '2017-01-19', 'hein'),
  (3, '2017-01-11', 'faux'),
  (3, '2017-01-15', 'vrai'),
  (3, '2017-01-14', 'vrai'),
  (1, '2017-01-28', 'hein')
--------------
 
--------------
select * from tabletest
--------------
 
+----+------+------------+-------+
| id | clef | datetest   | texte |
+----+------+------------+-------+
|  1 |    1 | 2017-01-18 | lol   |
|  2 |    1 | 2017-01-18 | vrai  |
|  3 |    1 | 2017-01-27 | faux  |
|  4 |    2 | 2017-01-07 | faux  |
|  5 |    2 | 2017-01-20 | vrai  |
|  6 |    3 | 2017-01-19 | hein  |
|  7 |    3 | 2017-01-11 | faux  |
|  8 |    3 | 2017-01-15 | vrai  |
|  9 |    3 | 2017-01-14 | vrai  |
| 10 |    1 | 2017-01-28 | hein  |
+----+------+------------+-------+
--------------
select      *,
            DATEDIFF(t.datetest, b.datebase) as diff
      from  tabletest as t
 
inner join  tablebase as b
        on  b.id = t.clef
 
  order by  b.id, t.id
--------------
 
+----+------+------------+-------+----+------------+------+
| id | clef | datetest   | texte | id | datebase   | diff |
+----+------+------------+-------+----+------------+------+
|  1 |    1 | 2017-01-18 | lol   |  1 | 2017-01-01 |   17 |
|  2 |    1 | 2017-01-18 | vrai  |  1 | 2017-01-01 |   17 |
|  3 |    1 | 2017-01-27 | faux  |  1 | 2017-01-01 |   26 |
| 10 |    1 | 2017-01-28 | hein  |  1 | 2017-01-01 |   27 |
|  4 |    2 | 2017-01-07 | faux  |  2 | 2017-01-02 |    5 |
|  5 |    2 | 2017-01-20 | vrai  |  2 | 2017-01-02 |   18 |
|  6 |    3 | 2017-01-19 | hein  |  3 | 2017-01-04 |   15 |
|  7 |    3 | 2017-01-11 | faux  |  3 | 2017-01-04 |    7 |
|  8 |    3 | 2017-01-15 | vrai  |  3 | 2017-01-04 |   11 |
|  9 |    3 | 2017-01-14 | vrai  |  3 | 2017-01-04 |   10 |
+----+------+------------+-------+----+------------+------+
--------------
select      *,
            DATEDIFF(t.datetest, b.datebase) as diff
      from  tabletest as t
 
inner join  tablebase as b
        on  b.id = t.clef
 
     where  t.id = ( select  max(id)
                       from  tabletest as z
                      where  z.clef  = t.clef)
 
  order by  b.id, t.id
--------------
 
+----+------+------------+-------+----+------------+------+
| id | clef | datetest   | texte | id | datebase   | diff |
+----+------+------------+-------+----+------------+------+
| 10 |    1 | 2017-01-28 | hein  |  1 | 2017-01-01 |   27 |
|  5 |    2 | 2017-01-20 | vrai  |  2 | 2017-01-02 |   18 |
|  9 |    3 | 2017-01-14 | vrai  |  3 | 2017-01-04 |   10 |
+----+------+------------+-------+----+------------+------+
--------------
select      *,
            DATEDIFF(t.datetest, b.datebase) as diff
      from  tabletest as t
 
inner join  tablebase as b
        on  b.id = t.clef
 
     where  t.id = ( select  max(id)
                       from  tabletest as z
                      where  z.clef  = t.clef)
       and  t.texte = 'vrai'
 
  order by  b.id, t.id
--------------
 
+----+------+------------+-------+----+------------+------+
| id | clef | datetest   | texte | id | datebase   | diff |
+----+------+------------+-------+----+------------+------+
|  5 |    2 | 2017-01-20 | vrai  |  2 | 2017-01-02 |   18 |
|  9 |    3 | 2017-01-14 | vrai  |  3 | 2017-01-04 |   10 |
+----+------+------------+-------+----+------------+------+
--------------
select      sum(DATEDIFF(t.datetest, b.datebase)) as cumul
      from  tabletest as t
 
inner join  tablebase as b
        on  b.id = t.clef
 
     where  t.id = ( select  max(id)
                       from  tabletest as z
                      where  z.clef  = t.clef)
       and  t.texte = 'vrai'
 
  order by  b.id, t.id
--------------
 
+-------+
| cumul |
+-------+
|    28 |
+-------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

1) j'ai décomposé en quatre requêtes la logique de ce que vous m'avez demandé de faire.

2) pourquoi sélectionner la ligne ayant la colonne "id" de la table "tabletest" la plus grande, plutôt que la colonne "datetest" la plus grande ?

3) je ne comprends pas trop, non plus, la priorité entre le test du §2 ci-dessus, et le test de la colonne "texte" à vrai.
J'ai respecté votre demande, mais pourquoi ne pas inclure dans le test de la "tabletest", la colonne "texte" = vrai, en plus de celui de la colonne "id" ?

@+

**Howely** · 22/01/2017, 20h51

hello!
d'abord un grand merci pour ton travail, je vais aller tester ça

le 1er point qui me saute au yeux est que je m'obstinais avec un order limit où vous utilisez plutôt (ou obligatoirement?) un max()

pour répondre à vos points 2 et 3, je n'ai donné que du contenu assez simple pour faire connaitre la logique de mon problème. En réalité, il y a plusieurs champs que je dois contrôler avec des exclusions ou non (qui remplacent le texte="vrai" ici). Du coup par exemple je peux pas prendre simplement la dernière date, la date en elle même est une information, elle ne sert pas à filtrer ou classer (on se retrouve quelques fois avec des dates qui ne se suivent pas (par exemple une ligne envoyée le 23/03 une autre le 24/03 et une autre le 15/03, il y a 2 systèmes indépendants connectés à la base, un site et un logiciel)

je sais pas si j'ai répondu à vos questions ou à coté

**Artemus24** · 22/01/2017, 22h31

Salut howely.

Je pensais qu'il y avait une erreur d'interprétation ou de faisabilité de votre part, dans le choix de la colonne "id" de la table "tabletest".

La colonne "texte" est-elle une variable logique ou devez-vous extraire une chaîne de caractères ?

@+

**Howely** · 23/01/2017, 02h09

hello

C'est effectivement un champs où je dois ignorer une liste de 7 chaines de caractères via not like '%%' (j'ai mis dans l'exemple un champs="vrai" pour aller plus vite)

J'ai donc testé votre solution, elle me retourne bien des résultats, par contre comparé au script php qui fait la même chose, j'ai des cumuls différents... du coup avec le nombre de lignes que j'ai, impossible de savoir réellement quelle version renvoie le bon nombre...

en tout cas merci, ca fonctionne bien et super rapide par rapport à ce que j'avais avant

je me permet de vous envoyer ma requête finale par mp, je sais pas si on peut améliorer, surtout au niveau des like

Tranformer 2 requêtes/traitements en un

Requêtes MySQL

Discussions similaires

Partager

Partager