Discussion :

[Ptikir]

Bonjour,
J'espère que je vais bien expliquer ma demande. Car là je sèche un peu...
Il s'agit de calculer le montant de différences entre une table et une table archivée.
En résultat d'une requête j'aimerai avoir un tableau qui ressemble à :
Type - Nombre - Montant
10 - 6 - 1154.23
11 - 1 - 273.24
12 - 2 - 425.78
etc...

Mes "types" sont définis comme suit :
Type 10 :

Code :

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

1
2
3
Select 10 AS type 
               FROM table_commune AS C 
             WHERE C.clef NOT IN (SELECT A.clef from table_archive AS A)

Type 11 :

Code :

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

1
2
3
4
Select 11 AS type 
               FROM table_commune AS C 
             WHERE C.clef IN (SELECT A.clef from table_archive AS A)
                 AND C.montant <> A.montant

Ensuite mes montants sont calculés selon un "type de tarif" (d'après tout un tas de paramètres de différentes tables). Mais pour faire plus simple ce serait grossomodo comme suit :
Type de tarif 1

Code :

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

1
2
3
4
5
Select (c.calcul * (t.index1 * 0,2) + t.index2 *0,8) as montant
   from table_commune as C, table_total as T
 where C.typeTarif = T.typetarif
    and t.annee = 2016
    and t.formule = 'test'

Type de tarif 2

Code :

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

1
2
3
4
5
Select (c.calcul * t.index1 * 0,2) as montant
   from table_commune as C, table_total as T
 where C.typeTarif = T.typetarif
    and t.annee = 2016
    and t.formule = 'test_2'

Donc je ne sais pas comment faire pour obtenir le tableau final.
Sachant que pour un type 10, on peut très bien avoir la formule de calcul du type tarif 1 ou type tarif 2

Je ne sais pas si je suis assez clair...

[Artemus24]

Salut Ptikir.

Cela me surprend que tu ne connaisses pas le "union" ???

Je ne sais pas d'où tu sors la colonne "nombre", mais tu ne donnes aucune indication dans ton message pour la traiter.

Voici ce que je proposes :

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
--------------
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 `total`
--------------
 
--------------
CREATE TABLE `total`
( `typetarif`  smallint unsigned NOT NULL primary key,
  `index1`     decimal(15,2)     NOT NULL,
  `index2`     decimal(15,2)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `total` (`typetarif`,`index1`,`index2`) values
  (22, 11.0, 89.0),
  (33, 25.0, 75.0),
  (55, 47.0, 53.0),
  (77, 33.0, 77.0)
--------------
 
--------------
select * from total
--------------
 
+-----------+--------+--------+
| typetarif | index1 | index2 |
+-----------+--------+--------+
|        22 |  11.00 |  89.00 |
|        33 |  25.00 |  75.00 |
|        55 |  47.00 |  53.00 |
|        77 |  33.00 |  77.00 |
+-----------+--------+--------+
--------------
DROP TABLE IF EXISTS `commune`
--------------
 
--------------
CREATE TABLE `commune`
( `clef`       integer  unsigned  NOT NULL primary key,
  `typetarif`  smallint unsigned  NOT NULL,
  `montant`    decimal(15,2)      NOT NULL,
  `calcul`     decimal(15,2)      NOT NULL,
  `annee`      smallint unsigned  NOT NULL,
  `formule`    varchar(255)       NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `commune` (`clef`,`typetarif`,`montant`,`calcul`,`annee`,`formule`) values
  (1, 22, 1200.0, 1200.0, 2016, 'test'),
  (2, 33,  800.0,  800.0, 2016, 'test_2'),
  (3, 55, 1500.0, 1500.0, 2016, 'test'),
  (4, 77,  400.0,  400.0, 2016, 'test_2')
--------------
 
--------------
select * from commune
--------------
 
+------+-----------+---------+---------+-------+---------+
| clef | typetarif | montant | calcul  | annee | formule |
+------+-----------+---------+---------+-------+---------+
|    1 |        22 | 1200.00 | 1200.00 |  2016 | test    |
|    2 |        33 |  800.00 |  800.00 |  2016 | test_2  |
|    3 |        55 | 1500.00 | 1500.00 |  2016 | test    |
|    4 |        77 |  400.00 |  400.00 |  2016 | test_2  |
+------+-----------+---------+---------+-------+---------+
--------------
DROP TABLE IF EXISTS `archive`
--------------
 
--------------
CREATE TABLE `archive`
( `clef`     integer unsigned NOT NULL primary key,
  `montant`  decimal(15,2)    NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `archive` (`clef`,`montant`) values
  (1, 1500.0), (4, 400.0)
--------------
 
--------------
select * from archive
--------------
 
+------+---------+
| clef | montant |
+------+---------+
|    1 | 1500.00 |
|    4 |  400.00 |
+------+---------+
--------------
select c.clef, 10 as type, null as montant
from commune as c
where not exists ( select 1 from archive as a where a.clef = c.clef )
 
union
 
select c.clef, 11 as type, null as montant
from commune as c
where     exists ( select 1 from archive as a where a.clef = c.clef and a.montant != c.montant )
 
union
 
select c.clef, null as type, cast((c.calcul * (t.index1 * 0.2) + (t.index2 * 0.8)) as decimal(15,2)) as montant
from       commune as c
inner join total   as t
on    t.typetarif = c.typetarif
where c.annee     = 2016
and   c.formule   = 'test'
 
union
 
select c.clef, null as type, cast((c.calcul * (t.index1 * 0.2))                    as decimal(15,2)) as montant
from       commune as c
inner join total   as t
on    t.typetarif = c.typetarif
where c.annee     = 2016
and   c.formule   = 'test_2'
 
order by clef
--------------
 
+------+------+----------+
| clef | type | montant  |
+------+------+----------+
|    1 |   11 |     NULL |
|    1 | NULL |  2711.20 |
|    2 |   10 |     NULL |
|    2 | NULL |  4000.00 |
|    3 |   10 |     NULL |
|    3 | NULL | 14142.40 |
|    4 | NULL |  2640.00 |
+------+------+----------+
--------------
select clef, max(type) as type, max(montant) as montant
from (
 
    select c.clef, 10 as type, null as montant
    from commune as c
    where not exists ( select 1 from archive as a where a.clef = c.clef )
 
    union
 
    select c.clef, 11 as type, null as montant
    from commune as c
    where     exists ( select 1 from archive as a where a.clef = c.clef and a.montant != c.montant )
 
    union
 
    select c.clef, null as type, cast((c.calcul * (t.index1 * 0.2) + (t.index2 * 0.8)) as decimal(15,2)) as montant
    from       commune as c
    inner join total   as t
    on    t.typetarif = c.typetarif
 
    where c.annee     = 2016
    and   c.formule   = 'test'
 
    union
 
    select c.clef, null as type, cast((c.calcul * (t.index1 * 0.2))                    as decimal(15,2)) as montant
    from       commune as c
    inner join total   as t
    on    t.typetarif = c.typetarif
 
    where c.annee     = 2016
    and   c.formule   = 'test_2'
) as x
 
group by clef
having type is not null
order by clef
--------------
 
+------+------+----------+
| clef | type | montant  |
+------+------+----------+
|    1 |   11 |  2711.20 |
|    2 |   10 |  4000.00 |
|    3 |   10 | 14142.40 |
+------+------+----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

Il faudra compléter ceci avec des index, des foreign key pour la performance.

@+

[Ptikir]

Salut Artemus,

Je connais le "union" en SQL :-) Alors ça fait un paquet d'années que je n'ai plus fait de SQL, mais lorsque j'écrivais ma requête, pour moi ce n'était pas la solution, donc peut-être que je me trompe, mais il y a quelque chose qui bloquait dans ma petite tête.

Il faut aussi que je précise je suis sur phpMyAdmin et il semblerait que lors s'une exécution d'un code SQL, seule la dernière requête s'exécute. (Jadis ) j'étais sur SQL Server et il me semble bien que l'on pouvait lancer moult requêtes à la suite, enfin bref...

Alors je vais essayer de mieux expliquer ce qu'il me faut.
Dans mon résultat le type, le nombre (de chaque type trouvé) et le montant total pour chaque type.

Pour chaque ligne de ma table table_commune qui ne se trouve pas dans la table table_archive
Cette ligne est de type 10. Donc je rajoute 1 au nombre et il faut encore que je lise le montant
Donc j'obtiens :
Type - Nombre - Montant
10 - 1 - 0,00

Ensuite pour obtenir le montant il faut que je définisse quel type de tarif j'ai pour cette ligne. (Ce n'est pas parce que j'ai mon type = 10 que je vais toujours avoir le même type de tarif).

Pour obtenir mon tableau final je me voyais plutôt commencé la requête comme suit :

Code :

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

1
2
3
4
5
 
Select type, count(*), sum(total) as montant
   from table_commune as C
 where ...
 group by type

Le problème que j'ai et il ne me semble pas l'avoir déjà fait. C'est de pouvoir utiliser c'est variables "crées" que sont type et total

Ci-dessous ce que je voyais comme requête complète peut-être que celà vous aidera à entrer dans ma petite tê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
 
Select type, count(*), sum(total)
   from table_commune
/* Type 10*/
 where ((select 10 as type 
             from table_commune
           where clef NOT IN(select clef from table_archive))
           OR
           (select 11 as type 
             from table_commune AS C
           where clef IN(select clef from table_archive AS A
                                           where C.montant > A.montant))
           /* d'autres "OR" pour chaque type */
           )
/* on détermine le calcul */
   and ((select (c.calcul * (t.index1 * 0,2) + t.index2 *0,8) as total
             from table_commune as C, table_total as T
           where C.typeTarif = T.typetarif
              and t.annee = 2016
              and t.formule = 'test')
          OR
          (Select (c.calcul * t.index1 * 0,2) as total
              from table_commune as C, table_total as T
            where C.typeTarif = T.typetarif
               and t.annee = 2016
               and t.formule = 'test_2'))

En tout cas je vais déjà essayer la dernière requête que tu m'as fourni Artemus. PAr contre je n'ai jamais utilisé le "cast". Et il faut que je refasse de l'exercice car le SQL est une gymnastique particulière.

[Artemus24]

Salut Ptikir.

lorsque j'écrivais ma requête, pour moi ce n'était pas la solution, donc peut-être que je me trompe, mais il y a quelque chose qui bloquait dans ma petite tête.

J'ai pensé que tu voulais conserver en l'état, tes quatre requêtes.
Du coup, ce qui m'est venu en premier à l'esprit, c'est de faire un "union".
Ainsi tu regroupes des lignes ayant des provenances différentes, dans ton cas, des requêtes différentes.
De plus, sous MySql, le "union" est très couteux en terme de performance.

Il faut aussi que je précise je suis sur phpMyAdmin et il semblerait que lors s'une exécution d'un code SQL, seule la dernière requête s'exécute.

Il faut séparer chaque requête par un point-virgule. Par exemple :

Code :

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

1
2
3
use mysql;
select * from user;
select * from user;

va bien vous produire trois résultats qui seront les uns derrières les autres dans la page de l'onglet sql de phpmyadmin.

Pour chaque ligne de ma table table_commune qui ne se trouve pas dans la table table_archive
Cette ligne est de type 10. Donc je rajoute 1 au nombre et il faut encore que je lise le montant

Dans l'exemple que j'ai donné, j'ai introduit la colonne "clef".
A l'affichage, je n'ai qu'une et une seule ligne pour une clef donnée.
De ce fait, je n'ai qu'un seul type et un seul calcul à produire.
Le traitement fait l'association des couples (clef; type) et (clef ; montant).

De plus, après avoir construit ma requête, je suppose qu'il est inutile de calculer le montant, si le type n'existe pas.

Ce n'est pas parce que j'ai mon type = 10 que je vais toujours avoir le même type de tarif

J'ai bien compris que le calcul n'avait aucun rapport avec le type et vice-versa.

Pour obtenir mon tableau final je me voyais plutôt commencé la requête comme suit

Donc la requête avec un "union" ne vous plait pas !

Je vous rappelle que je découvre votre problème et tant bien que mal, j'essaye de trouver une solution satisfaisante.
Nous sommes sur un forum et nous pouvons échanger sur le résultat à produire, en fonction de vos besoin et de vos contraintes, que je ne suis pas censé connaitre quand vous postez ce sujet.

Par contre je n'ai jamais utilisé le "cast".

Le "cast" sert à modifier le type utilisé.
Comme le résultat se nomme "montant", et est du même nom que la colonne dans la table "équipement", j'ai fait en sorte de mettre "decimal(15,2)".
Si vous n'en voulez pas, vous pouvez le supprimer, mais mysql va faire une conversion implicite !

Je vous propose une autre façon de procéder la réunification des quatre requêtes.
Je rappelle que dans ma première requête, j'obtiens le résultat suivant :

Code :

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

1
2
3
4
5
6
7
+------+------+----------+
| clef | type | montant  |
+------+------+----------+
|    1 |   11 |  2711.20 |
|    2 |   10 |  4000.00 |
|    3 |   10 | 14142.40 |
+------+------+----------+

Il y a trois lignes au total, dont deux avec le type 10 et une avec le type 11.

Et voici la nouvelle requête, sans "union" :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--------------
select case           when a.montant != c.montant then 11
                      when a.clef is null         then 10
                      else null end as type,
   sum(case c.formule when 'test'                 then cast((c.calcul * (t.index1 * 0.2) + (t.index2 * 0.8)) as decimal(15,2))
                      when 'test_2'               then cast((c.calcul * (t.index1 * 0.2))                    as decimal(15,2))
                      else null end) as montant,
   count(distinct c.clef) as nombre
 
from            commune as c
 
left outer join archive as a
on    a.clef      = c.clef
 
inner      join total   as t
on    t.typetarif = c.typetarif
 
where c.annee     = 2016
 
group by type
having   type is not null
order by type
--------------
 
+------+----------+--------+
| type | montant  | nombre |
+------+----------+--------+
|   10 | 18142.40 |      2 |
|   11 |  2711.20 |      1 |
+------+----------+--------+

On retrouve bien deux et un pour la colonne "nombre".

Est-ce que cela vous convient

Et il faut que je refasse de l'exercice car le SQL est une gymnastique particulière.

C'est un peu particulier comme exercice intellectuel qui diffère de la programmation procédurale.
Et je ne parle même pas de la programme par objet, et encore moins la programmation parallèle.

P.S.: pour perfectionner cette requête, il me faudrait un jeu d'essai plus consistant que celui que j'ai fait !

@+