Discussion :

[sebastien.bordat]

Bonjour

Nous avons une table (Table_3) avec un INDEX UNIQUE sur deux champs (qui sont des clés étrangères égalements).

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
Table_1
id
PRIMARY KEY (id)
Engine=Innodb
 
Table_2
id
PRIMARY KEY (id)
Engine=Innodb
 
Table_3
id
table_1_id
table_2_id
montant
PRIMARY KEY (id)
UNIQUE INDEX (table_1_id, table_2_id)
CONSTRAINT FOREIGN KEY (table_1_id) REFERENCE Table_1 (id)
CONSTRAINT FOREIGN KEY (table_2_id) REFERENCE Table_2 (id)
Engine=Innodb

La Table_3 est mis à jour par des TRIGGERS insérés dans différentes tables afin de calculer des montants.
Nous faisons des INSERT ... ON DUPLICATE KEY ... pour mettre à jour cette table.

Nous nous sommes rendu compte que des doublons avaient été insérés dans cette table.

Nous avons donc fait un CHECK TABLE Table_3, qui nous a indiqué que l'INDEX UNIQUE était corrompu, d'où le soucis de doublons je suppose.
Pour résoudre le problème, nous avons renomé la Table_3 puis l'avons récrée entièrement en recalculant tous les montants (Nous avions essayés un REPAIR TABLE mais non fonctionnelle en Innodb).
Mais des doublons ont de nouveaux été insérés dans la table (environ 3000 doublons sur 2 millions).

Questions :

Un INDEX UNIQUE sur plusieurs champs peut il poser des soucis en Innodb ?
Qu'est ce qui peut faire que même après avoir recréé la table entièrement, nous avons de nouveau des doublons ? Ce peut il que ce soit un soucis au niveau des fichiers MySQL ?
Que nous conseillez vous pour réparer cette erreur ?

Mysql est en version 5.5.38.
La Table_3 contient environ 2 millions d'enregistrements.

Merci

[SQLpro]

Que nous conseillez vous pour réparer cette erreur ?

Changez de GBBD ! MySQL est farci de bugs... ! A lire : https://blog.developpez.com/sqlpro/p...oudre_aux_yeux
Ils en sont à pus de 80000 bugs dont une grande partie n'est toujours pas résolus ...

Alternatives : PostGreSQL, SQL Server Express ou version payante....

A +

[Artemus24]

Salut sebastien.bordat.

Vous voyez le niveau intellectuel désolant de SQLPRO qui, sur un problème sérieux, se permet de critiquer vos choix.
Ne croyez pas tout ce qu'il dit car il est resté borné sur la version mysql d'il y a dix ans !

Nous faisons des INSERT ... ON DUPLICATE KEY ... pour mettre à jour cette table.

Pour que le "duplicate key" fonctionne, il faut une contrainte d'unicité sur votre couple (table_1_id ; table_2_id).

Nous nous sommes rendu compte que des doublons avaient été insérés dans cette table.

Justement de quel doublon parlez-vous ? Sont-ce ceux du "unique index" ?

Nous avons donc fait un CHECK TABLE Table_3, qui nous a indiqué que l'INDEX UNIQUE était corrompu

La corruption d'un index n'arrive pas par hasard.
A l'exécution de vos scripts, vous n'avez jamais eu de messages d'avertissements ?
Et faire de temps en temps de la maintenance, ne fait pas de mal, tout au contraire !

Mais des doublons ont de nouveaux été insérés dans la table (environ 3000 doublons sur 2 millions).

Ce qui serait intéressant, c'est de reproduire ces fameux doublons qui vous posent des problèmes.
Je suis parti de l'hypothèse que le doublon se fait sur le couple (table_1_id ; table_2_id).

Un INDEX UNIQUE sur plusieurs champs peut il poser des soucis en Innodb ?

Telle que la question est posée, on ne peut que répondre que par la négative.
Nous utilisons des "unique index" depuis fort longtemps, et nous n'avons jamais rencontré des problèmes de cette nature.

Qu'est ce qui peut faire que même après avoir recréé la table entièrement, nous avons de nouveau des doublons ?

Ça, c'est la bonne question qu'il faut se poser !

Ce peut-il que ce soit un soucis au niveau des fichiers MySQL ?

Non !

Que nous conseillez vous pour réparer cette erreur ?

La première des choses est d'essayer de reproduire le cas où vous avez des doublons.

La piste que je soupçonne repose sur la déclarative de vos deux colonnes "table_1_id" & "table_2_id". Vous nous avez donné juste le nom de la colonne et rien de plus.
Un "unique index" autorise le NULL ! Donc si vous avez inséré des NULL dans vos deux colonnes, il y aura des doublons !
Donc vous devez faire en sorte de mettre "not null" sur vos deux colonnes.

Ce que je propose de faire à la place de votre solution c'est d'inverser le rôle de la primary key et de l'"unique index".
Voici votre nouvelle primary key :

Code :

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

primary key (`table_1_id`,`table_2_id`)

et voici votre nouvel index :

Code :

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

unique index `idx` (`id`)

Ceci vous oblige d'avoir des clefs étrangères non null, mais aussi que le couple (table_1_id ; table_2_id) sera unique.

Voici un petit test pour illustrer mes propos :

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
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
--------------
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 `pere_one`
--------------
 
--------------
CREATE TABLE `pere_one`
( `one_id`      integer unsigned not null auto_increment primary key,
  `one_nombre`  varchar(255)     not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `pere_one` (`one_nombre`) values
  ('un'),('deux'),('trois'),('quatre'),('cinq')
--------------
 
--------------
select * from `pere_one`
--------------
 
+--------+------------+
| one_id | one_nombre |
+--------+------------+
|      1 | un         |
|      2 | deux       |
|      3 | trois      |
|      4 | quatre     |
|      5 | cinq       |
+--------+------------+
--------------
DROP TABLE IF EXISTS `pere_two`
--------------
 
--------------
CREATE TABLE `pere_two`
( `two_id`       integer unsigned not null auto_increment primary key,
  `two_couleur`  varchar(255)     not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `pere_two` (`two_couleur`) values
  ('rouge'),('vert'),('jaune'),('bleu')
--------------
 
--------------
select * from `pere_two`
--------------
 
+--------+-------------+
| two_id | two_couleur |
+--------+-------------+
|      1 | rouge       |
|      2 | vert        |
|      3 | jaune       |
|      4 | bleu        |
+--------+-------------+
--------------
DROP TABLE IF EXISTS `fils`
--------------
 
--------------
CREATE TABLE `fils`
( `fils_id`      integer unsigned not null auto_increment primary key,
  `pere_one_id`  integer unsigned     null,
  `pere_two_id`  integer unsigned     null,
  `quantite`     decimal(15,3)    not null,
  UNIQUE INDEX `idx` (`pere_one_id`,`pere_two_id`),
  CONSTRAINT `FK_PERE_ONE` FOREIGN KEY (`pere_one_id`) REFERENCES `pere_one` (`one_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_PERE_TWO` FOREIGN KEY (`pere_two_id`) REFERENCES `pere_two` (`two_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (1,1,10)
--------------
 
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (2,2,10)
--------------
 
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (1,1,10)
--------------
 
ERROR 1062 (23000) at line 92: Duplicata du champ '1-1' pour la clef 'idx'
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (2,2,10)
--------------
 
ERROR 1062 (23000) at line 93: Duplicata du champ '2-2' pour la clef 'idx'
--------------
select * from `fils`
--------------
 
+---------+-------------+-------------+----------+
| fils_id | pere_one_id | pere_two_id | quantite |
+---------+-------------+-------------+----------+
|       1 |           1 |           1 |   10.000 |
|       2 |           2 |           2 |   10.000 |
+---------+-------------+-------------+----------+
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (null,null,10)
--------------
 
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (null,null,10)
--------------
 
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (null,null,10)
--------------
 
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (null,null,10)
--------------
 
--------------
select * from `fils`
--------------
 
+---------+-------------+-------------+----------+
| fils_id | pere_one_id | pere_two_id | quantite |
+---------+-------------+-------------+----------+
|       1 |           1 |           1 |   10.000 |
|       2 |           2 |           2 |   10.000 |
|       5 |        NULL |        NULL |   10.000 |
|       6 |        NULL |        NULL |   10.000 |
|       7 |        NULL |        NULL |   10.000 |
|       8 |        NULL |        NULL |   10.000 |
+---------+-------------+-------------+----------+
--------------
DROP TABLE IF EXISTS `fils`
--------------
 
--------------
CREATE TABLE `fils`
( `fils_id`      integer unsigned not null auto_increment,
  `pere_one_id`  integer unsigned not null,
  `pere_two_id`  integer unsigned not null,
  `quantite`     decimal(15,3)    not null,
  primary key (`pere_one_id`,`pere_two_id`),
  unique index `idx` (`fils_id`),
  CONSTRAINT `FK_PERE_ONE` FOREIGN KEY (`pere_one_id`) REFERENCES `pere_one` (`one_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_PERE_TWO` FOREIGN KEY (`pere_two_id`) REFERENCES `pere_two` (`two_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (1,1,10)
--------------
 
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (2,2,10)
--------------
 
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (1,1,10)
--------------
 
ERROR 1062 (23000) at line 141: Duplicata du champ '1-1' pour la clef 'PRIMARY'
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (2,2,10)
--------------
 
ERROR 1062 (23000) at line 142: Duplicata du champ '2-2' pour la clef 'PRIMARY'
--------------
select * from `fils`
--------------
 
+---------+-------------+-------------+----------+
| fils_id | pere_one_id | pere_two_id | quantite |
+---------+-------------+-------------+----------+
|       1 |           1 |           1 |   10.000 |
|       2 |           2 |           2 |   10.000 |
+---------+-------------+-------------+----------+
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (null,null,10)
--------------
 
ERROR 1048 (23000) at line 154: Le champ 'pere_one_id' ne peut être vide (null)
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (null,null,10)
--------------
 
ERROR 1048 (23000) at line 155: Le champ 'pere_one_id' ne peut être vide (null)
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (null,null,10)
--------------
 
ERROR 1048 (23000) at line 156: Le champ 'pere_one_id' ne peut être vide (null)
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (null,null,10)
--------------
 
ERROR 1048 (23000) at line 157: Le champ 'pere_one_id' ne peut être vide (null)
--------------
select * from `fils`
--------------
 
+---------+-------------+-------------+----------+
| fils_id | pere_one_id | pere_two_id | quantite |
+---------+-------------+-------------+----------+
|       1 |           1 |           1 |   10.000 |
|       2 |           2 |           2 |   10.000 |
+---------+-------------+-------------+----------+
--------------
truncate `fils`
--------------
 
--------------
select * from `fils`
--------------
 
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (1,1,10) on duplicate key update quantite = quantite + values(`quantite`)
--------------
 
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (2,2,10) on duplicate key update quantite = quantite + values(`quantite`)
--------------
 
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (1,1,10) on duplicate key update quantite = quantite + values(`quantite`)
--------------
 
--------------
insert into `fils` (`pere_one_id`,`pere_two_id`,`quantite`) values  (2,2,10) on duplicate key update quantite = quantite + values(`quantite`)
--------------
 
--------------
select * from `fils`
--------------
 
+---------+-------------+-------------+----------+
| fils_id | pere_one_id | pere_two_id | quantite |
+---------+-------------+-------------+----------+
|       1 |           1 |           1 |   20.000 |
|       2 |           2 |           2 |   20.000 |
+---------+-------------+-------------+----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

[sebastien.bordat]

Bonjour Artemus

Il y a bien une contrainte d'unicité sur table_1_id, table_2_id, et les deux champs ne peuvent pas être NULL.
Voici la déclaration complète de la table actuellement :

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE table_3 (
id INT(11) NOT NULL AUTO_INCREMENT,
table_1_id INT(11) NOT NULL,
table_2_id INT(11) NOT NULL,
montant DECIMAL(10,2) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE INDEX (table_1_id, table_2_id),
CONSTRAINT FOREIGN KEY (table_1_id) REFERENCES table_1 (id),
CONSTRAINT FOREIGN KEY (table_2_id) REFERENCES table_2 (id)
) Engine=InnoDB

Les doublons sont bien sur l'INDEX UNIQUE (table_1_id, table_2_id).

Cette table est rempli par des TRIGGERS et par une PROCEDURE SQL qui me permet de recalculer entièrement la table.
J'ai l'impression que c'est uniquement depuis cette PROCEDURE SQL que les doublons se créent.

J'ai également remarqué ce matin que lorsque je fait :

Code :

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

SELECT * FROM table_3 WHERE table_1_id = XX AND table_2_id = YY

correspondant logiquement à un doublon, un seul enregistrement était retourné...

Alors que si je fais un

Code :

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

SELECT * FROM table_3 WHERE table_1_id = XX

ça me retourne bien deux enregistrements avec table_2_id = YY.
Mais peut être est ce le fait qu'il y ait un INDEX UNIQUE ?


Pour le moment, j'ai modifié ma PROCEDURE pour ne plus utiliser le ON DUPLICATE KEY, afin de voir si mes TRIGGERS ont également le soucis.

[Artemus24]

Salut sebastien.bordat.

Il y a bien une contrainte d'unicité sur table_1_id, table_2_id, et les deux champs ne peuvent pas être NULL.

C'est l'hypothèse de départ.

J'ai également remarqué ce matin que lorsque je fais :

Code :

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

SELECT * FROM table_3 WHERE table_1_id = XX AND table_2_id = YY

correspondant logiquement à un doublon, un seul enregistrement était retourné...

Alors que si je fais un

Code :

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

SELECT * FROM table_3 WHERE table_1_id = XX

ça me retourne bien deux enregistrements avec table_2_id = YY.
Mais peut être est ce le fait qu'il y ait un INDEX UNIQUE ?

Si je suis votre raisonnement, il n'y a pas de doublons lorsque vous précisez les deux colonnes de votre index unique.
Tandis que vous avez des doublons (???) si vous précisez seulement la première colonne (table_1_id).
Comme je ne peux pas vérifier votre affirmation, je pense plutôt à un problème d'affichage.

Avez-vous fait le test suivant :

Code :

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

1
2
3
4
5
6
select    table_1_id,
          table_2_id,
          count(*) as qte
    from  `table_3`
group by  table_1_id, table_2_id
  having  qte > 2;

Si vous n'obtenez aucun résultat, c'est qu'il n'y a pas de doublons.

Au cas où le test vous donne quelque chose, pourriez-vous me communiquer :
--> vos trois tables
--> un jeu d'essai représentatif de votre problèmes
--> les triggers
--> la procédure stockées.

Vous pouvez m'envoyer le tout à mon adresse email : artemus @ jcz.fr

@+

[escartefigue]

Bonjour,

Vous n'avez pas communiqué le DDL du create index de votre index unique non primaire, seule la création de l'index pour l'id primaire est implicite

[escartefigue]

Avez-vous fait le test suivant :

Code :

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

1
2
3
4
5
6
select    table_1_id,
          table_2_id,
          count(*) as qte
    from  `table_3`
group by  table_1_id, table_2_id
  having  qte > 2;

Si vous n'obtenez aucun résultat, c'est qu'il n'y a pas de doublons.

Sauf qu'il faut filtrer avec HAVING count(*) > 1 et non 2

Au cas où le test vous donne quelque chose, pourriez-vous me communiquer :
Vous pouvez m'envoyer le tout à mon adresse email : artemus @ jcz.fr

Non ! un forum est un lieu d'échange partagé. Pour que tous les participants puissent bénéficier des éléments de réflexion, la règle est justement de proscrire les échanges privés

@sebastien.bordat : avez vous créé l'index unique, cf. mon message précédent

[sebastien.bordat]

Si je suis votre raisonnement, il n'y a pas de doublons lorsque vous précisez les deux colonnes de votre index unique.
Tandis que vous avez des doublons (???) si vous précisez seulement la première colonne (table_1_id).

Exactement, aucun doublon lorsque je sélectionne sur les deux colonnes de l'index unique, mais des doublons lorsque je sélectionne sur la première colonne.

Avez-vous fait le test suivant :

Code :

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

1
2
3
4
5
6
select    table_1_id,
          table_2_id,
          count(*) as qte
    from  `table_3`
group by  table_1_id, table_2_id
  having  qte > 1;

Si vous n'obtenez aucun résultat, c'est qu'il n'y a pas de doublons.

Oui, c'est justement cette requête qui m'avait permis de voir que j'avais des doublons dans ma table.

Je vous met la liste des tables :

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
CREATE TABLE `group` (
	id INT(11) NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (id)
) Engine=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE patient (
	id INT(11) NOT NULL AUTO_INCREMENT,
	group_id INT(11) NOT NULL,
	PRIMARY KEY (id),
	KEY group_index (group_id),
	CONSTRAINT FOREIGN KEY (group_id) REFERENCES group (id)
) Engine=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE user (
	id INT(11) NOT NULL AUTO_INCREMENT,
	group_id INT(11) NOT NULL,
	PRIMARY KEY (id),
	KEY group_index (group_id),
	CONSTRAINT FOREIGN KEY (group_id) REFERENCES group (id)
) Engine=InnoDB DEFAULT CHARSET=latin1;
 
CREATE TABLE patient_user (
	id INT(11) NOT NULL AUTO_INCREMENT,
	patient_id INT(11) NOT NULL,
	user_id INT(11) NOT NULL,
	amount DECIMAL(10,2)
	PRIMARY KEY (id),
	UNIQUE KEY patient_user_index (patient_id, user_id),
	KEY user_index (user_id),
	CONSTRAINT FOREIGN KEY (patient_id) REFERENCES patient (id),
	CONSTRAINT FOREIGN KEY (user_id) REFERENCES user (id)
) Engine=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE care (
	id INT(11) NOT NULL AUTO_INCREMENT,
	patient_id INT(11) NOT NULL,
	user_id INT(11) NOT NULL,
	amount DECIMAL(10,2),
	PRIMARY KEY (id),
	KEY patient_index (patient_id),
	KEY user_index (user_id),
	CONSTRAINT FOREIGN KEY (patient_id) REFERENCES patient (id),
	CONSTRAINT FOREIGN KEY (user_id) REFERENCES user (id)
) Engine=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE payment (
	id INT(11) NOT NULL AUTO_INCREMENT,
	patient_id INT(11) NOT NULL,
	user_id INT(11) NOT NULL,
	amount DECIMAL(10,2),
	PRIMARY KEY (id),
	KEY patient_index (patient_id),
	KEY user_index (user_id),
	CONSTRAINT FOREIGN KEY (patient_id) REFERENCES patient (id),
	CONSTRAINT FOREIGN KEY (user_id) REFERENCES user (id)
) Engine=InnoDB DEFAULT CHARSET=utf8;

Ainsi que les TRIGGERS (il y a des TRIGGERS également sur les AFTER UPDATE et AFTER DELETE mais c'est exactement le même code) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER care_after_insert AFTER INSERT ON care FOR EACH ROW
BEGIN
	INSERT INTO patient_user (patient_id, user_id, amount)
	VALUES (patient_id, user_id, amount)
	ON DUPLICATE KEY amount = amount + VALUES(amount);
END
 
CREATE TRIGGER payment_after_insert AFTER INSERT ON payment FOR EACH ROW
BEGIN
	INSERT INTO patient_user (patient_id, user_id, amount)
	VALUES (patient_id, user_id, -amount)
	ON DUPLICATE KEY amount = amount - VALUES(amount);
END

Et la PROCEDURE qui me permet de remettre à jour la table :

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
CREATE PROCEDURE P_amount(IN group_id INT(11))
BEGIN
	DECLARE var_user_id INT;
	DECLARE done INT DEFAULT FALSE;
	DECLARE curs CURSOR FOR SELECT id FROM user WHERE CASE WHEN group_id IS NOT NULL THEN group = group_id ELSE 1 = 1 END;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
	-- -----------------------
	-- Drop Temporary Table --
	-- -----------------------
 
	DROP TEMPORARY TABLE IF EXISTS temp_care;
	DROP TEMPORARY TABLE IF EXISTS temp_payment;
 
	-- ------------------
	-- Temporary Table --
	-- ------------------
 
	CREATE TEMPORARY TABLE temp_care (
		id INT AUTO_INCREMENT,
		patient_id INT,
		amount DECIMAL(10,2),
		PRIMARY KEY (id),
		INDEX (user_id),
		INDEX (patient_id)
	);
 
	CREATE TEMPORARY TABLE temp_payment (
		id INT AUTO_INCREMENT,
		patient_id INT,
		amount DECIMAL(10,2),
		PRIMARY KEY (id),
		INDEX (user_id),
		INDEX (patient_id)
	);
 
	OPEN curs;
	read_loop: LOOP
 
		FETCH curs INTO var_user_id;
 
		IF done
		THEN
			LEAVE read_loop;
		END IF;
 
		-- --------
		-- Soins --
		-- --------
 
		INSERT INTO temp_care (patient_id, amount)
		SELECT
			patient_id,
			amount
		FROM care
		WHERE user_id = var_user_id;
 
		-- -------------
		-- Règlements --
		-- -------------
 
		INSERT INTO temp_payment (patient_id, amount)
		SELECT
			patient_id,
			amount
		FROM payment
		WHERE user_id = var_user_id;
 
		-- -----------
		-- Montants --
		-- -----------
 
		UPDATE patient_user
		SET amount = 0
		WHERE user_id = var_user_id;
 
		INSERT INTO patient_user (patient_id, user_id, amount)
		SELECT patient_id, var_user_id, SUM(amount)
		FROM temp_care
		GROUP BY patient_id
		ON DUPLICATE KEY UPDATE amount = amount + VALUES(amount);
 
		INSERT INTO patient_user (patient_id, user_id, amount)
		SELECT patient_id, var_user_id, -SUM(amount)
		FROM temp_payment
		GROUP BY patient_id
		ON DUPLICATE KEY UPDATE amount = amount + VALUES(amount);
 
		-- -----------------
		-- Truncate Table --
		-- -----------------
 
		TRUNCATE TABLE temp_care;
		TRUNCATE TABLE temp_payment;
 
	END LOOP;
	CLOSE curs;
 
	-- -----------------------
	-- Drop Temporary Table --
	-- -----------------------
 
	DROP TEMPORARY TABLE temp_care;
	DROP TEMPORARY TABLE temp_payment;
 
END

Dans les exemples que j'avais donné dans mon premier message :
Table_1 correspond à patient.
Table_2 correspond à user.
Table_3 correspond à patient_user.

Je rappelle également que je n'ai qu'environ 3000 doublons sur 1.6 millions, et que ça va de 1 doublons pour certains utilisateurs à une centaine pour d'autres.

[Artemus24]

Salut à tous.

@ Escartefigue : vous avez raison, j'ai déposé le test des doublons un peu trop rapidement.
En effet, c'est soit "having nbre > 1" ou "having nbre >= 2".
Et je constate que "sebastien.bordat" a corrigé de lui-même mon erreur !

Non ! un forum est un lieu d'échange partagé. Pour que tous les participants puissent bénéficier des éléments de réflexion, la règle est justement de proscrire les échanges privés

Dans le principe, je suis d'accord avec vous, mais que faites-vous de la confidentialité ?
C'est à "sebastien.bordat" de juger ce qui doit être divulgé ou pas, et pour cause car il peut se faire taper sur les doigts par son chef.
Et puis, il y a la volumétrie de l'export de la base de données afin de reproduire le problème qui ne passera pas dans un simple message.


@ sebastien.bordat : merci de nous avoir communiqué vos tables et vos déclencheurs et procédure stockée.
J'aurai aimé avoir aussi un jeu d'essai afin de comprendre et de reproduire le cas de vos doublons. Je vais faire avec !

Je vais tester votre exemple, et un peu plus tard dans la journée, je vous communiquerai les résultats.

@+

[escartefigue]

Dans le principe, je suis d'accord avec vous, mais que faites-vous de la confidentialité ?
C'est à "sebastien.bordat" de juger ce qui doit être divulgé ou pas, et pour cause car il peut se faire taper sur les doigts par son chef.
Et puis, il y a la volumétrie de l'export de la base de données afin de reproduire le problème qui ne passera pas dans un simple message.

Les mesures valables pour lutter contre le piratage sont l'anonymisation, le cryptage et l'accès restreint par mot de passe.
Un message privé ne garantit en rien la sécurité, et contrevient aux principes de base d'un forum, qui par vocation, est collaboratif

Sinon, je me permets d'insister OU EST LE DDL DU CREATE INDEX POUR L'INDEX UNIQUE NON PRIMAIRE

[sebastien.bordat]

@ Escartefigue :

Le fait d'ajouter "UNIQUE KEY patient_user_index (patient_id, user_id)" lors de la création de la table n'est il pas suffisant pour créer l'index unique non primaire ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE patient_user (
	id INT(11) NOT NULL AUTO_INCREMENT,
	patient_id INT(11) NOT NULL,
	user_id INT(11) NOT NULL,
	amount DECIMAL(10,2)
	PRIMARY KEY (id),
	UNIQUE KEY patient_user_index (patient_id, user_id),
	KEY user_index (user_id),
	CONSTRAINT FOREIGN KEY (patient_id) REFERENCES patient (id),
	CONSTRAINT FOREIGN KEY (user_id) REFERENCES user (id)
) Engine=InnoDB DEFAULT CHARSET=utf8;

Comment puis je faire pour vous donner le DDL du CREATE INDEX ?

Sinon, voila le résultat d'un SHOW INDEX FROM patient_user :

Table

Non_unique

Key_name

Seq_in_index

Column_name

Collation

Cardinality

Sub_part

Packed

Null

Index_type

Comment

Index

patient_user

0

PRIMARY

1

id

A

1623980

NULL

NULL

BTREE

patient_user

0

patient_user_index

1

patient_id

A

1623980

NULL

NULL

BTREE

patient_user

0

patient_user_index

2

user_id

A

1623980

NULL

NULL

BTREE

patient_user

1

user_index

1

user_id

A

370

NULL

NULL

BTREE

@ Artemus24 :

Voila par exemple les données d'un patient + utilisateur qui ont eu des doublons lors de l’exécution de la procédure :

Code :

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

1
2
3
4
5
INSERT INTO group VALUES (224);
INSERT INTO patient VALUES (2655100, 224);
INSERT INTO user VALUES (788, 224);
INSERT INTO care VALUES (30945959, 2655100, 788, 28.92), (30945961, 2655100, 788, 15.96), (31328757, 2655100, 788, 23.00);
INSERT INTO payment VALUES (7118210, 2655100, 788, 44.88), (7225482, 2655100, 788, 23.00);

Et le résultat :

Code :

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

INSERT INTO patient_user VALUES (2655100, 788, 67.88), (2655100, 788, -67.88);

La procédure m'a crée deux lignes : une avec la somme des soins, et une avec la somme des paiements.

Sinon, j'ai fait un dump de la table pour test en local et quand je fais une sélection sur les deux colonnes de l'index unique, j'obtiens bien les doublons.
Il doit donc y avoir un soucis sur ma table en production.

J'ai également modifié ma procédure pour ne plus passer par des INSERT ... ON DUPLICATE KEY UPDATE ... mais par des SELECT ... IF ... INSERT ... ELSE ... UPDATE afin de ne plus interroger l'INDEX UNIQUE, le temps de comprendre pourquoi j'ai ce soucis de doublons.

[Artemus24]

Salut à tous.

Les mesures valables pour lutter contre le piratage sont l'anonymisation, le cryptage et l'accès restreint par mot de passe.

Je ne parle pas de cela, mais de donner à quelqu'un, entre autre moi puisque sebastien.bordat ne me connait, des informations sensibles pour son projet.

Un message privé ne garantit en rien la sécurité, et contrevient aux principes de base d'un forum, qui par vocation, est collaboratif

Je suis d'accord qu'un forum a pour vocation d'être collaboratif, mais sebastien.bordat n'est pas non plus obligé de fournir des informations sensibles.

Sinon, je me permets d'insister OU EST LE DDL DU CREATE INDEX POUR L'INDEX UNIQUE NON PRIMAIRE

Cela s'adresse à qui ? A moi, artemus24 ou à sebastien.bordat ?
Car l'information a été donné dans le message #4 par sebastien.bordat :

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE table_3 (
id INT(11) NOT NULL AUTO_INCREMENT,
table_1_id INT(11) NOT NULL,
table_2_id INT(11) NOT NULL,
montant DECIMAL(10,2) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE INDEX (table_1_id, table_2_id),
CONSTRAINT FOREIGN KEY (table_1_id) REFERENCES table_1 (id),
CONSTRAINT FOREIGN KEY (table_2_id) REFERENCES table_2 (id)
) Engine=InnoDB

Je le mets en rouge pour le faire ressortir !

Le fait d'ajouter "UNIQUE KEY patient_user_index (patient_id, user_id)" lors de la création de la table n'est il pas suffisant pour créer l'index unique non primaire ?

C'est largement suffisant !

Message suivant : l'analyse que j'ai faite de votre base de données.

@+

[Artemus24]

Salut à tous.

1) je constate dans vos tables qu'une fois vous utilisez "charset=utf8" et une autre fois, vous utilisez "charset=latin1".
Normalisez vos tables en mettant partout soit l'un, soit l'autre mais pas les deux. Je vous conseille de mettre "latin1" si vous utilisez que le français et l'anglais car vos chaînes de caractères prendront moins de place en stockage.
En latin1, 1 caractère = 1 octet. En utf8, 1 caractère occupe de 1 à 3 octets.

2) je constate aussi que vous créez des index sur les colonnes de vos clef étrangères.
Cela ne sert à rien puisque la clef étrangère sous MySql est déjà un index.
Autrement dit, ceci ne sert à rien :

Code :

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

1
2
KEY group_index (group_id),
CONSTRAINT FOREIGN KEY (group_id) REFERENCES group (id)

3) quand je déclare une colonne technique de ce type là :

Code :

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

id INT(11) NOT NULL AUTO_INCREMENT,

qui est auto incrémenté, et dont la valeur ne peut jamais être négative, je préfère mettre :

Code :

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

id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

En mettant seulement "integer", votre amplitude va de 0 à 2.147.483.647.
En mettant "integer unsigned ", votre amplitude va de 0 à 4.294.967.295.
Si vous avez peur de ne pas avoir assez d'amplitude, mettez "bigint unsigned" dont l'amplitude va de 0 à 18.446.744.073.709.551.615.

4) j'ai pris l'habitude d'être bavard dans mes déclarations et de ne jamais travailler avec les valeurs par défaut. Ainsi je préfère mettre ceci :

Code :

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

CONSTRAINT `FK_GROUPE` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

C'est juste un détail, mais cela a son importance.

5) remarque sur le paragraphe précédent.
Quand on fait un dictionnaire des données, il est nécessaire de faire en sorte qu'une même colonne porte le même nom dans la totalité de la base de données.
Ainsi la colonne "id" est l'identifiant et de surcroît votre clef primaire d'une quelconque table.
On peut confondre cette colonne avec une autre table ce qui est source d'erreur.
Je vous conseille de mettre partout "group_id", aussi bien dans la table "group", que dans les tables "user" et patient".
Même remarque aussi pour les tables patient et user.

6) les clefs étrangères de la table "care" ne sont pas correctes.
Il faut faire pointer la table "care" sur la table "patient_user" et non séparément sur les tables "patient" et "user" séparées.
Autrement dit, vous devez déclarer :

Code :

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

1
2
3
4
5
6
7
CREATE TABLE care (
	id INT(11) NOT NULL AUTO_INCREMENT,
	patient_user_id INT(11) NOT NULL,
	amount DECIMAL(10,2),
	PRIMARY KEY (id),
	CONSTRAINT FOREIGN KEY (patient_user_id) REFERENCES patient_user (id),
) Engine=InnoDB DEFAULT CHARSET=utf8;

Même remarque sur table "payment".

Code :

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

1
2
3
4
5
6
7
CREATE TABLE payment (
	id INT(11) NOT NULL AUTO_INCREMENT,
	patient_user_id INT(11) NOT NULL,
	amount DECIMAL(10,2),
	PRIMARY KEY (id),
	CONSTRAINT FOREIGN KEY (patient_usr_id) REFERENCES patient_user (id),
) Engine=InnoDB DEFAULT CHARSET=utf8;

Sinon à quoi vous sert la table "patient_user" ?

Les tables "care" et payment" sont des tables filles de la table "patient_user". En les dissociant, vous pouvez avoir un problème de doublons.

7) éviter d'utiliser des mots réservés en tant que nom de colonnes, par exemple avec "group", "user", "amount".
Vous êtes français, alors pourquoi anglicisez-vous ces noms de colonnes ?

8) les déclencheurs sont inutiles !!!
En tout cas, je n'aurai jamais procédé comme vous le faites.
Je préfère le classique "update". Voir mon exemple sur les tables "care" et "payment" (voir requête N°5 et 7).

9) après avoir créé un jeu d'essai, je me suis aperçu que vous avez un problème avec la gestion du "groupe".
La modélisation n'est pas correcte car on peut associer un "patient" avec un "user" qui n'appartiennent pas au même groupe.

Ne sachant pas comment sont gérés les patients et les users dans votre projets, j'ai laissé en l'état la gestion du "groupe".

Un patient peut avoir plusieurs utilisateur (de 1 à N).
Un utilisateur peut avoir plusieurs patients (de 1 à N).

En quoi, il est interdit qu'un patient d'un groupe, puisse consulter un utilisateur d'un autre groupe ?
C'est cette notion de groupe que j'ai du mal à intégrer dans votre modèle.
Le groupe se rapporte à quoi ?

10) La procédure est aussi inutile. Et surtout, je ne comprends pas trop sont utilités.
Une requête classique fait aussi bien l'affaire !
On ne fait pas une procédure pour le plaisir d'en faire, mais pour résoudre un problème qui ne peut pas se faire avec des requêtes.
De plus, vous devez aussi gérer vos accès par le transactionnel !

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
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
--------------
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 `group`
--------------
 
--------------
CREATE TABLE `group`
( `group_id`   integer unsigned not null auto_increment primary key,
  `group_lib`  varchar(255)     not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `group` (`group_lib`) values
  ('groupe 1'),('groupe 2'),('groupe 3')
--------------
 
--------------
select * from `group`
--------------
 
+----------+-----------+
| group_id | group_lib |
+----------+-----------+
|        1 | groupe 1  |
|        2 | groupe 2  |
|        3 | groupe 3  |
+----------+-----------+
--------------
DROP TABLE IF EXISTS `patient`
--------------
 
--------------
CREATE TABLE `patient`
( `patient_id`   integer unsigned not null auto_increment primary key,
  `patient_lib`  varchar(255)     not null,
  `group_id`     integer unsigned not null,
  CONSTRAINT `fk_patient_group` FOREIGN KEY (`group_id`) REFERENCES `group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `patient` (`patient_lib`,`group_id`) values
  ('patient 10',1),('patient 11',1),('patient 12',1),
  ('patient 20',2),('patient 21',2),('patient 22',2),
  ('patient 30',3),('patient 31',3),('patient 32',3)
--------------
 
--------------
select * from `patient`
--------------
 
+------------+-------------+----------+
| patient_id | patient_lib | group_id |
+------------+-------------+----------+
|          1 | patient 10  |        1 |
|          2 | patient 11  |        1 |
|          3 | patient 12  |        1 |
|          4 | patient 20  |        2 |
|          5 | patient 21  |        2 |
|          6 | patient 22  |        2 |
|          7 | patient 30  |        3 |
|          8 | patient 31  |        3 |
|          9 | patient 32  |        3 |
+------------+-------------+----------+
--------------
select      g.group_lib,
            p.patient_lib
      from  `patient` as p
 
inner join  `group`   as g
        on  g.group_id = p.group_id
--------------
 
+-----------+-------------+
| group_lib | patient_lib |
+-----------+-------------+
| groupe 1  | patient 10  |
| groupe 1  | patient 11  |
| groupe 1  | patient 12  |
| groupe 2  | patient 20  |
| groupe 2  | patient 21  |
| groupe 2  | patient 22  |
| groupe 3  | patient 30  |
| groupe 3  | patient 31  |
| groupe 3  | patient 32  |
+-----------+-------------+
--------------
DROP TABLE IF EXISTS `user`
--------------
 
--------------
CREATE TABLE `user`
( `user_id`      integer unsigned not null auto_increment primary key,
  `user_lib`     varchar(255)     not null,
  `group_id`     integer unsigned not null,
  CONSTRAINT `fk_user_group` FOREIGN KEY (`group_id`) REFERENCES `group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `user` (`user_lib`,`group_id`) values
  ('utilisateur 10',1),('utilisateur 11',1),('utilisateur 12',1),
  ('utilisateur 20',2),('utilisateur 21',2),('utilisateur 22',2),
  ('utilisateur 30',3),('utilisateur 31',3),('utilisateur 32',3)
--------------
 
--------------
select * from `user`
--------------
 
+---------+----------------+----------+
| user_id | user_lib       | group_id |
+---------+----------------+----------+
|       1 | utilisateur 10 |        1 |
|       2 | utilisateur 11 |        1 |
|       3 | utilisateur 12 |        1 |
|       4 | utilisateur 20 |        2 |
|       5 | utilisateur 21 |        2 |
|       6 | utilisateur 22 |        2 |
|       7 | utilisateur 30 |        3 |
|       8 | utilisateur 31 |        3 |
|       9 | utilisateur 32 |        3 |
+---------+----------------+----------+
--------------
select      g.group_lib,
            u.user_lib
      from  `user`   as u
 
inner join  `group`  as g
        on  g.group_id = u.group_id
--------------
 
+-----------+----------------+
| group_lib | user_lib       |
+-----------+----------------+
| groupe 1  | utilisateur 10 |
| groupe 1  | utilisateur 11 |
| groupe 1  | utilisateur 12 |
| groupe 2  | utilisateur 20 |
| groupe 2  | utilisateur 21 |
| groupe 2  | utilisateur 22 |
| groupe 3  | utilisateur 30 |
| groupe 3  | utilisateur 31 |
| groupe 3  | utilisateur 32 |
+-----------+----------------+
--------------
select      g.group_lib,
            group_concat(distinct u.user_lib)    as user_lib,
            group_concat(distinct p.patient_lib) as patient_lib
      from  `group`   as g
 
inner join  `user`    as u
        on  u.group_id = g.group_id
 
inner join  `patient` as p
        on  p.group_id = g.group_id
 
  group by  g.group_id
--------------
 
+-----------+----------------------------------------------+----------------------------------+
| group_lib | user_lib                                     | patient_lib                      |
+-----------+----------------------------------------------+----------------------------------+
| groupe 1  | utilisateur 10,utilisateur 11,utilisateur 12 | patient 10,patient 11,patient 12 |
| groupe 2  | utilisateur 20,utilisateur 21,utilisateur 22 | patient 20,patient 21,patient 22 |
| groupe 3  | utilisateur 30,utilisateur 31,utilisateur 32 | patient 30,patient 31,patient 32 |
+-----------+----------------------------------------------+----------------------------------+
--------------
DROP TABLE IF EXISTS `patient_user`
--------------
 
--------------
CREATE TABLE `patient_user`
( `id`           integer unsigned not null auto_increment primary key,
  `amount`       decimal(15,2)    not null,
  `patient_id`   integer unsigned not null,
  `user_id`      integer unsigned not null,
  unique index `idx` (`patient_id`,`user_id`),
  CONSTRAINT `fk_user_patient_patient` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`patient_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_user_patient_user`    FOREIGN KEY (`user_id`)    REFERENCES `user`    (`user_id`)    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `patient_user` (`amount`,`patient_id`,`user_id`) values
  (0.0, 1, 1), (0.0, 2, 1), (0.0, 1, 2), (0.0, 3, 3),
  (0.0, 4, 4), (0.0, 5, 4), (0.0, 4, 5), (0.0, 6, 6),
  (0.0, 7, 7), (0.0, 8, 7), (0.0, 7, 8), (0.0, 9, 9),
  (0.0, 1, 7), (0.0, 8, 2), (0.0, 5, 3), (0.0, 6, 8)
--------------
 
--------------
select * from `patient_user`
--------------
 
+----+--------+------------+---------+
| id | amount | patient_id | user_id |
+----+--------+------------+---------+
|  1 |   0.00 |          1 |       1 |
|  2 |   0.00 |          2 |       1 |
|  3 |   0.00 |          1 |       2 |
|  4 |   0.00 |          3 |       3 |
|  5 |   0.00 |          4 |       4 |
|  6 |   0.00 |          5 |       4 |
|  7 |   0.00 |          4 |       5 |
|  8 |   0.00 |          6 |       6 |
|  9 |   0.00 |          7 |       7 |
| 10 |   0.00 |          8 |       7 |
| 11 |   0.00 |          7 |       8 |
| 12 |   0.00 |          9 |       9 |
| 13 |   0.00 |          1 |       7 |
| 14 |   0.00 |          8 |       2 |
| 15 |   0.00 |          5 |       3 |
| 16 |   0.00 |          6 |       8 |
+----+--------+------------+---------+
--------------
select       x.id,
             u.user_lib,
                         p.patient_lib,
            g1.group_lib as group_user,
                        g2.group_lib as group_patient,
                        case when g1.group_id <> g2.group_id then 'pas bon' else 'ok' end as test
      from  `patient_user` as x
 
inner join  `user`         as u
        on  u.user_id    = x.user_id
 
inner join  `patient`      as p
        on  p.patient_id = x.patient_id
 
inner join  `group`        as g1
        on  g1.group_id  = p.group_id
 
inner join  `group`        as g2
        on  g2.group_id  = u.group_id
--------------
 
+----+----------------+-------------+------------+---------------+---------+
| id | user_lib       | patient_lib | group_user | group_patient | test    |
+----+----------------+-------------+------------+---------------+---------+
|  1 | utilisateur 10 | patient 10  | groupe 1   | groupe 1      | ok      |
|  3 | utilisateur 11 | patient 10  | groupe 1   | groupe 1      | ok      |
|  2 | utilisateur 10 | patient 11  | groupe 1   | groupe 1      | ok      |
|  4 | utilisateur 12 | patient 12  | groupe 1   | groupe 1      | ok      |
| 15 | utilisateur 12 | patient 21  | groupe 2   | groupe 1      | pas bon |
| 14 | utilisateur 11 | patient 31  | groupe 3   | groupe 1      | pas bon |
|  5 | utilisateur 20 | patient 20  | groupe 2   | groupe 2      | ok      |
|  7 | utilisateur 21 | patient 20  | groupe 2   | groupe 2      | ok      |
|  6 | utilisateur 20 | patient 21  | groupe 2   | groupe 2      | ok      |
|  8 | utilisateur 22 | patient 22  | groupe 2   | groupe 2      | ok      |
| 13 | utilisateur 30 | patient 10  | groupe 1   | groupe 3      | pas bon |
| 16 | utilisateur 31 | patient 22  | groupe 2   | groupe 3      | pas bon |
|  9 | utilisateur 30 | patient 30  | groupe 3   | groupe 3      | ok      |
| 11 | utilisateur 31 | patient 30  | groupe 3   | groupe 3      | ok      |
| 10 | utilisateur 30 | patient 31  | groupe 3   | groupe 3      | ok      |
| 12 | utilisateur 32 | patient 32  | groupe 3   | groupe 3      | ok      |
+----+----------------+-------------+------------+---------------+---------+
--------------
DROP TABLE IF EXISTS `care`
--------------
 
--------------
CREATE TABLE `care`
( `care_id`           integer unsigned not null auto_increment primary key,
  `care_amount`       decimal(15,2)    not null,
  `patient_user_id`   integer unsigned not null,
  CONSTRAINT `fk_care_patient_user` FOREIGN KEY (`patient_user_id`) REFERENCES `patient_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `care` (`care_amount`,`patient_user_id`) values
  (10.0, 1), (12.0, 2), (14.0, 3), (16.0, 4), (18.0, 5), (20.0, 6), (22.0, 7), (24.0, 8),
  (26.0, 9), (28.0,10), (30.0,11), (32.0,12), (34.0,13), (36.0,14), (38.0,15), (40.0,16),
  (40.0, 1), (38.0, 2), (36.0, 3), (34.0, 4), (32.0, 5), (30.0, 6), (28.0, 7), (26.0, 8),
  (24.0, 9), (22.0,10), (20.0,11), (18.0,12), (16.0,13), (14.0,14), (12.0,15), (10.0,16)
--------------
 
--------------
select * from `care`
--------------
 
+---------+-------------+-----------------+
| care_id | care_amount | patient_user_id |
+---------+-------------+-----------------+
|       1 |       10.00 |               1 |
|       2 |       12.00 |               2 |
|       3 |       14.00 |               3 |
|       4 |       16.00 |               4 |
|       5 |       18.00 |               5 |
|       6 |       20.00 |               6 |
|       7 |       22.00 |               7 |
|       8 |       24.00 |               8 |
|       9 |       26.00 |               9 |
|      10 |       28.00 |              10 |
|      11 |       30.00 |              11 |
|      12 |       32.00 |              12 |
|      13 |       34.00 |              13 |
|      14 |       36.00 |              14 |
|      15 |       38.00 |              15 |
|      16 |       40.00 |              16 |
|      17 |       40.00 |               1 |
|      18 |       38.00 |               2 |
|      19 |       36.00 |               3 |
|      20 |       34.00 |               4 |
|      21 |       32.00 |               5 |
|      22 |       30.00 |               6 |
|      23 |       28.00 |               7 |
|      24 |       26.00 |               8 |
|      25 |       24.00 |               9 |
|      26 |       22.00 |              10 |
|      27 |       20.00 |              11 |
|      28 |       18.00 |              12 |
|      29 |       16.00 |              13 |
|      30 |       14.00 |              14 |
|      31 |       12.00 |              15 |
|      32 |       10.00 |              16 |
+---------+-------------+-----------------+
--------------
update      `patient_user` as x
inner join  (    select  patient_user_id,
                         sum(care_amount) as debit
                   from  `care`
               group by  patient_user_id
            ) as c
        on  c.patient_user_id = x.id
 
       set  x.amount = x.amount - c.debit
--------------
 
--------------
select * from `patient_user`
--------------
 
+----+--------+------------+---------+
| id | amount | patient_id | user_id |
+----+--------+------------+---------+
|  1 | -50.00 |          1 |       1 |
|  2 | -50.00 |          2 |       1 |
|  3 | -50.00 |          1 |       2 |
|  4 | -50.00 |          3 |       3 |
|  5 | -50.00 |          4 |       4 |
|  6 | -50.00 |          5 |       4 |
|  7 | -50.00 |          4 |       5 |
|  8 | -50.00 |          6 |       6 |
|  9 | -50.00 |          7 |       7 |
| 10 | -50.00 |          8 |       7 |
| 11 | -50.00 |          7 |       8 |
| 12 | -50.00 |          9 |       9 |
| 13 | -50.00 |          1 |       7 |
| 14 | -50.00 |          8 |       2 |
| 15 | -50.00 |          5 |       3 |
| 16 | -50.00 |          6 |       8 |
+----+--------+------------+---------+
--------------
select       u.user_lib,
             p.patient_lib,
             sum(c.care_amount)  as debit,
             any_value(x.amount) as total
 
      from  `care`         as c
 
inner join  `patient_user` as x
        on  x.id         = c.patient_user_id
 
inner join  `user`         as u
        on  u.user_id    = x.user_id
 
inner join  `patient`      as p
        on  p.patient_id = x.patient_id
 
  group by  u.user_id, p.patient_id
  order by  u.user_id, p.patient_id
--------------
 
+----------------+-------------+-------+--------+
| user_lib       | patient_lib | debit | total  |
+----------------+-------------+-------+--------+
| utilisateur 10 | patient 10  | 50.00 | -50.00 |
| utilisateur 10 | patient 11  | 50.00 | -50.00 |
| utilisateur 11 | patient 10  | 50.00 | -50.00 |
| utilisateur 11 | patient 31  | 50.00 | -50.00 |
| utilisateur 12 | patient 12  | 50.00 | -50.00 |
| utilisateur 12 | patient 21  | 50.00 | -50.00 |
| utilisateur 20 | patient 20  | 50.00 | -50.00 |
| utilisateur 20 | patient 21  | 50.00 | -50.00 |
| utilisateur 21 | patient 20  | 50.00 | -50.00 |
| utilisateur 22 | patient 22  | 50.00 | -50.00 |
| utilisateur 30 | patient 10  | 50.00 | -50.00 |
| utilisateur 30 | patient 30  | 50.00 | -50.00 |
| utilisateur 30 | patient 31  | 50.00 | -50.00 |
| utilisateur 31 | patient 22  | 50.00 | -50.00 |
| utilisateur 31 | patient 30  | 50.00 | -50.00 |
| utilisateur 32 | patient 32  | 50.00 | -50.00 |
+----------------+-------------+-------+--------+
--------------
DROP TABLE IF EXISTS `payment`
--------------
 
--------------
CREATE TABLE `payment`
( `payment_id`        integer unsigned not null auto_increment primary key,
  `payment_amount`    decimal(15,2)    not null,
  `patient_user_id`   integer unsigned not null,
  CONSTRAINT `fk_payment_patient_user` FOREIGN KEY (`patient_user_id`) REFERENCES `patient_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `payment` (`payment_amount`,`patient_user_id`) values
  (12.0, 1), (25.0, 2), (40.0, 3), (18.0, 4), (34.0, 5), (21.0, 6), (40.0, 7), (11.0, 8),
  (44.0, 9), (16.0,10), (23.0,11), (26.0,12), (15.0,13), (31.0,14), (35.0,15), (38.0,16),
  (38.0, 1), (25.0, 2), (10.0, 3), (32.0, 4), (16.0, 5), (29.0, 6), ( 0.0, 7), (39.0, 8),
  ( 6.0, 9), (34.0,10), (17.0,11), (24.0,12), (35.0,13), (19.0,14), (15.0,15), (12.0,16)
--------------
 
--------------
select * from `payment`
--------------
 
+------------+----------------+-----------------+
| payment_id | payment_amount | patient_user_id |
+------------+----------------+-----------------+
|          1 |          12.00 |               1 |
|          2 |          25.00 |               2 |
|          3 |          40.00 |               3 |
|          4 |          18.00 |               4 |
|          5 |          34.00 |               5 |
|          6 |          21.00 |               6 |
|          7 |          40.00 |               7 |
|          8 |          11.00 |               8 |
|          9 |          44.00 |               9 |
|         10 |          16.00 |              10 |
|         11 |          23.00 |              11 |
|         12 |          26.00 |              12 |
|         13 |          15.00 |              13 |
|         14 |          31.00 |              14 |
|         15 |          35.00 |              15 |
|         16 |          38.00 |              16 |
|         17 |          38.00 |               1 |
|         18 |          25.00 |               2 |
|         19 |          10.00 |               3 |
|         20 |          32.00 |               4 |
|         21 |          16.00 |               5 |
|         22 |          29.00 |               6 |
|         23 |           0.00 |               7 |
|         24 |          39.00 |               8 |
|         25 |           6.00 |               9 |
|         26 |          34.00 |              10 |
|         27 |          17.00 |              11 |
|         28 |          24.00 |              12 |
|         29 |          35.00 |              13 |
|         30 |          19.00 |              14 |
|         31 |          15.00 |              15 |
|         32 |          12.00 |              16 |
+------------+----------------+-----------------+
--------------
update      `patient_user` as x
inner join  (    select  patient_user_id,
                         sum(payment_amount) as credit
                   from  `payment`
               group by  patient_user_id
            ) as p
        on  p.patient_user_id = x.id
 
       set  x.amount = x.amount + p.credit
--------------
 
--------------
select * from `patient_user`
--------------
 
+----+--------+------------+---------+
| id | amount | patient_id | user_id |
+----+--------+------------+---------+
|  1 |   0.00 |          1 |       1 |
|  2 |   0.00 |          2 |       1 |
|  3 |   0.00 |          1 |       2 |
|  4 |   0.00 |          3 |       3 |
|  5 |   0.00 |          4 |       4 |
|  6 |   0.00 |          5 |       4 |
|  7 | -10.00 |          4 |       5 |
|  8 |   0.00 |          6 |       6 |
|  9 |   0.00 |          7 |       7 |
| 10 |   0.00 |          8 |       7 |
| 11 | -10.00 |          7 |       8 |
| 12 |   0.00 |          9 |       9 |
| 13 |   0.00 |          1 |       7 |
| 14 |   0.00 |          8 |       2 |
| 15 |   0.00 |          5 |       3 |
| 16 |   0.00 |          6 |       8 |
+----+--------+------------+---------+
--------------
select       u.user_lib,
             p.patient_lib,
             sum(z.payment_amount)  as credit,
             any_value(x.amount)    as total
 
      from  `payment`      as z
 
inner join  `patient_user` as x
        on  x.id         = z.patient_user_id
 
inner join  `user`         as u
        on  u.user_id    = x.user_id
 
inner join  `patient`      as p
        on  p.patient_id = x.patient_id
 
  group by  u.user_id, p.patient_id
  order by  u.user_id, p.patient_id
--------------
 
+----------------+-------------+--------+--------+
| user_lib       | patient_lib | credit | total  |
+----------------+-------------+--------+--------+
| utilisateur 10 | patient 10  |  50.00 |   0.00 |
| utilisateur 10 | patient 11  |  50.00 |   0.00 |
| utilisateur 11 | patient 10  |  50.00 |   0.00 |
| utilisateur 11 | patient 31  |  50.00 |   0.00 |
| utilisateur 12 | patient 12  |  50.00 |   0.00 |
| utilisateur 12 | patient 21  |  50.00 |   0.00 |
| utilisateur 20 | patient 20  |  50.00 |   0.00 |
| utilisateur 20 | patient 21  |  50.00 |   0.00 |
| utilisateur 21 | patient 20  |  40.00 | -10.00 |
| utilisateur 22 | patient 22  |  50.00 |   0.00 |
| utilisateur 30 | patient 10  |  50.00 |   0.00 |
| utilisateur 30 | patient 30  |  50.00 |   0.00 |
| utilisateur 30 | patient 31  |  50.00 |   0.00 |
| utilisateur 31 | patient 22  |  50.00 |   0.00 |
| utilisateur 31 | patient 30  |  40.00 | -10.00 |
| utilisateur 32 | patient 32  |  50.00 |   0.00 |
+----------------+-------------+--------+--------+
--------------
drop view if exists `vue_racap`
--------------
 
--------------
create view `vue_recap` as
select       u.user_lib,
             p.patient_lib,
             t1.credit,
             t2.debit,
            (t2.debit - t1.credit) as diff,
             x.amount      as total
 
      from  `patient_user` as x
 
inner join  `user`         as u
        on  u.user_id    = x.user_id
 
inner join  `patient`      as p
        on  p.patient_id = x.patient_id
 
inner join  (  select  patient_user_id,
                       sum(care_amount) as credit
                 from  `care`
             group by  patient_user_id
            ) as t1
        on  t1.patient_user_id = x.id
 
inner join  (  select  patient_user_id,
                       sum(payment_amount) as debit
                 from  `payment`
             group by  patient_user_id
            ) as t2
        on  t2.patient_user_id = x.id
 
  order by  x.id
--------------
 
--------------
select * from vue_recap
--------------
 
+----------------+-------------+--------+-------+--------+--------+
| user_lib       | patient_lib | credit | debit | diff   | total  |
+----------------+-------------+--------+-------+--------+--------+
| utilisateur 10 | patient 10  |  50.00 | 50.00 |   0.00 |   0.00 |
| utilisateur 10 | patient 11  |  50.00 | 50.00 |   0.00 |   0.00 |
| utilisateur 11 | patient 10  |  50.00 | 50.00 |   0.00 |   0.00 |
| utilisateur 12 | patient 12  |  50.00 | 50.00 |   0.00 |   0.00 |
| utilisateur 20 | patient 20  |  50.00 | 50.00 |   0.00 |   0.00 |
| utilisateur 20 | patient 21  |  50.00 | 50.00 |   0.00 |   0.00 |
| utilisateur 21 | patient 20  |  50.00 | 40.00 | -10.00 | -10.00 |
| utilisateur 22 | patient 22  |  50.00 | 50.00 |   0.00 |   0.00 |
| utilisateur 30 | patient 30  |  50.00 | 50.00 |   0.00 |   0.00 |
| utilisateur 30 | patient 31  |  50.00 | 50.00 |   0.00 |   0.00 |
| utilisateur 31 | patient 30  |  50.00 | 40.00 | -10.00 | -10.00 |
| utilisateur 32 | patient 32  |  50.00 | 50.00 |   0.00 |   0.00 |
| utilisateur 30 | patient 10  |  50.00 | 50.00 |   0.00 |   0.00 |
| utilisateur 11 | patient 31  |  50.00 | 50.00 |   0.00 |   0.00 |
| utilisateur 12 | patient 21  |  50.00 | 50.00 |   0.00 |   0.00 |
| utilisateur 31 | patient 22  |  50.00 | 50.00 |   0.00 |   0.00 |
+----------------+-------------+--------+-------+--------+--------+
--------------
drop view if exists `vue_patient`
--------------
 
--------------
create view `vue_patient` as
select      p.patient_lib,
            t1.total,
            t2.credit,
            t3.debit,
            (t3.debit - t2.credit) as diff
 
      from  `patient` as p
 
inner join  (    select  patient_id,
                         sum(amount) as total
                   from  `patient_user`
               group by  patient_id
            ) as t1
        on  t1.patient_id = p.patient_id
 
inner join  (    select  x1.patient_id,
                         sum(c.care_amount) as credit
                   from  `care`             as c
 
             inner join  `patient_user`     as x1
                     on  x1.id = c.patient_user_id
 
               group by  x1.patient_id
            ) as t2
        on  t2.patient_id = p.patient_id
 
inner join  (    select  x2.patient_id,
                         sum(t.payment_amount) as debit
                   from  `payment`             as t
 
             inner join  `patient_user`      as x2
                     on  x2.id = t.patient_user_id
 
               group by  x2.patient_id
            ) as t3
        on  t3.patient_id = p.patient_id
 
  group by  p.patient_id
--------------
 
--------------
select * from vue_patient
--------------
 
+-------------+--------+--------+--------+--------+
| patient_lib | total  | credit | debit  | diff   |
+-------------+--------+--------+--------+--------+
| patient 10  |   0.00 | 150.00 | 150.00 |   0.00 |
| patient 11  |   0.00 |  50.00 |  50.00 |   0.00 |
| patient 12  |   0.00 |  50.00 |  50.00 |   0.00 |
| patient 20  | -10.00 | 100.00 |  90.00 | -10.00 |
| patient 21  |   0.00 | 100.00 | 100.00 |   0.00 |
| patient 22  |   0.00 | 100.00 | 100.00 |   0.00 |
| patient 30  | -10.00 | 100.00 |  90.00 | -10.00 |
| patient 31  |   0.00 | 100.00 | 100.00 |   0.00 |
| patient 32  |   0.00 |  50.00 |  50.00 |   0.00 |
+-------------+--------+--------+--------+--------+
--------------
select * from vue_patient where total <> 0
--------------
 
+-------------+--------+--------+-------+--------+
| patient_lib | total  | credit | debit | diff   |
+-------------+--------+--------+-------+--------+
| patient 20  | -10.00 | 100.00 | 90.00 | -10.00 |
| patient 30  | -10.00 | 100.00 | 90.00 | -10.00 |
+-------------+--------+--------+-------+--------+
--------------
drop view if exists `vue_user`
--------------
 
--------------
create view `vue_user` as
select      u.user_lib,
            t1.total,
            t2.credit,
            t3.debit,
            (t3.debit - t2.credit) as diff
 
      from  `user` as u
 
inner join  (    select  user_id,
                         sum(amount) as total
                   from  `patient_user`
               group by  user_id
            ) as t1
        on  t1.user_id = u.user_id
 
inner join  (    select  x1.user_id,
                         sum(c.care_amount) as credit
                   from  `care`             as c
 
             inner join  `patient_user`     as x1
                     on  x1.id = c.patient_user_id
 
               group by  x1.user_id
            ) as t2
        on  t2.user_id = u.user_id
 
inner join  (    select  x2.user_id,
                         sum(t.payment_amount) as debit
                   from  `payment`             as t
 
             inner join  `patient_user`      as x2
                     on  x2.id = t.patient_user_id
 
               group by  x2.user_id
            ) as t3
        on  t3.user_id = u.user_id
 
  group by  u.user_id
--------------
 
--------------
select * from vue_user
--------------
 
+----------------+--------+--------+--------+--------+
| user_lib       | total  | credit | debit  | diff   |
+----------------+--------+--------+--------+--------+
| utilisateur 10 |   0.00 | 100.00 | 100.00 |   0.00 |
| utilisateur 11 |   0.00 | 100.00 | 100.00 |   0.00 |
| utilisateur 12 |   0.00 | 100.00 | 100.00 |   0.00 |
| utilisateur 20 |   0.00 | 100.00 | 100.00 |   0.00 |
| utilisateur 21 | -10.00 |  50.00 |  40.00 | -10.00 |
| utilisateur 22 |   0.00 |  50.00 |  50.00 |   0.00 |
| utilisateur 30 |   0.00 | 150.00 | 150.00 |   0.00 |
| utilisateur 31 | -10.00 | 100.00 |  90.00 | -10.00 |
| utilisateur 32 |   0.00 |  50.00 |  50.00 |   0.00 |
+----------------+--------+--------+--------+--------+
--------------
select * from vue_user where total <> 0
--------------
 
+----------------+--------+--------+-------+--------+
| user_lib       | total  | credit | debit | diff   |
+----------------+--------+--------+-------+--------+
| utilisateur 21 | -10.00 |  50.00 | 40.00 | -10.00 |
| utilisateur 31 | -10.00 | 100.00 | 90.00 | -10.00 |
+----------------+--------+--------+-------+--------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Jetez un coup d’œil à mon exemple.

@+

[Artemus24]

Je vous communique mon script sql :

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
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
SET AUTOCOMMIT = 0;
START TRANSACTION;
 
SET collation_connection = latin1_general_ci;
 
-- ======================
-- Base de Données `base`
-- ======================
 
DROP DATABASE IF EXISTS `base`;
 
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`;
 
use `base`;
 
-- =============
-- Table `group`
-- =============
 
DROP TABLE IF EXISTS `group`;
 
CREATE TABLE `group`
( `group_id`   integer unsigned not null auto_increment primary key,
  `group_lib`  varchar(255)     not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- ======================
-- Insertion dans `group`
-- ======================
 
insert into `group` (`group_lib`) values
  ('groupe 1'),('groupe 2'),('groupe 3');
 
-- =================
-- Vidage de `group`
-- =================
 
select * from `group`;
 
-- ===============
-- Table `patient`
-- ===============
 
DROP TABLE IF EXISTS `patient`;
 
CREATE TABLE `patient`
( `patient_id`   integer unsigned not null auto_increment primary key,
  `patient_lib`  varchar(255)     not null,
  `group_id`     integer unsigned not null,
  CONSTRAINT `fk_patient_group` FOREIGN KEY (`group_id`) REFERENCES `group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- ========================
-- Insertion dans `patient`
-- ========================
 
insert into `patient` (`patient_lib`,`group_id`) values
  ('patient 10',1),('patient 11',1),('patient 12',1),
  ('patient 20',2),('patient 21',2),('patient 22',2),
  ('patient 30',3),('patient 31',3),('patient 32',3);
 
-- ===================
-- Vidage de `patient`
-- ===================
 
select * from `patient`;
 
-- ===========
-- Requête N°1
-- ===========
 
select      g.group_lib,
            p.patient_lib
      from  `patient` as p
 
inner join  `group`   as g
        on  g.group_id = p.group_id;
 
-- ============
-- Table `user`
-- ============
 
DROP TABLE IF EXISTS `user`;
 
CREATE TABLE `user`
( `user_id`      integer unsigned not null auto_increment primary key,
  `user_lib`     varchar(255)     not null,
  `group_id`     integer unsigned not null,
  CONSTRAINT `fk_user_group` FOREIGN KEY (`group_id`) REFERENCES `group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- =====================
-- Insertion dans `user`
-- =====================
 
insert into `user` (`user_lib`,`group_id`) values
  ('utilisateur 10',1),('utilisateur 11',1),('utilisateur 12',1),
  ('utilisateur 20',2),('utilisateur 21',2),('utilisateur 22',2),
  ('utilisateur 30',3),('utilisateur 31',3),('utilisateur 32',3);
 
-- ================
-- Vidage de `user`
-- ================
 
select * from `user`;
 
-- ===========
-- Requête N°2
-- ===========
 
select      g.group_lib,
            u.user_lib
      from  `user`   as u
 
inner join  `group`  as g
        on  g.group_id = u.group_id;
 
-- ===========
-- Requête N°3
-- ===========
 
select      g.group_lib,
            group_concat(distinct u.user_lib)    as user_lib,
            group_concat(distinct p.patient_lib) as patient_lib
      from  `group`   as g
 
inner join  `user`    as u
        on  u.group_id = g.group_id
 
inner join  `patient` as p
        on  p.group_id = g.group_id
 
  group by  g.group_id;
 
-- ====================
-- Table `patient_user`
-- ====================
 
DROP TABLE IF EXISTS `patient_user`;
 
CREATE TABLE `patient_user`
( `id`           integer unsigned not null auto_increment primary key,
  `amount`       decimal(15,2)    not null,
  `patient_id`   integer unsigned not null,
  `user_id`      integer unsigned not null,
  unique index `idx` (`patient_id`,`user_id`),
  CONSTRAINT `fk_user_patient_patient` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`patient_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_user_patient_user`    FOREIGN KEY (`user_id`)    REFERENCES `user`    (`user_id`)    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- =============================
-- Insertion dans `patient_user`
-- =============================
 
insert into `patient_user` (`amount`,`patient_id`,`user_id`) values
  (0.0, 1, 1), (0.0, 2, 1), (0.0, 1, 2), (0.0, 3, 3),
  (0.0, 4, 4), (0.0, 5, 4), (0.0, 4, 5), (0.0, 6, 6),
  (0.0, 7, 7), (0.0, 8, 7), (0.0, 7, 8), (0.0, 9, 9),
  (0.0, 1, 7), (0.0, 8, 2), (0.0, 5, 3), (0.0, 6, 8);
 
-- ========================
-- Vidage de `patient_user`
-- ========================
 
select * from `patient_user`;
 
-- ===========
-- Requête N°4
-- ===========
 
select       x.id,
             u.user_lib,
			 p.patient_lib,
            g1.group_lib as group_user,
			g2.group_lib as group_patient,
			case when g1.group_id <> g2.group_id then 'pas bon' else 'ok' end as test
      from  `patient_user` as x
 
inner join  `user`         as u
        on  u.user_id    = x.user_id
 
inner join  `patient`      as p
        on  p.patient_id = x.patient_id
 
inner join  `group`        as g1
        on  g1.group_id  = p.group_id
 
inner join  `group`        as g2
        on  g2.group_id  = u.group_id;
 
-- ============
-- Table `care`
-- ============
 
DROP TABLE IF EXISTS `care`;
 
CREATE TABLE `care`
( `care_id`           integer unsigned not null auto_increment primary key,
  `care_amount`       decimal(15,2)    not null,
  `patient_user_id`   integer unsigned not null,
  CONSTRAINT `fk_care_patient_user` FOREIGN KEY (`patient_user_id`) REFERENCES `patient_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- =====================
-- Insertion dans `care`
-- =====================
 
insert into `care` (`care_amount`,`patient_user_id`) values
  (10.0, 1), (12.0, 2), (14.0, 3), (16.0, 4), (18.0, 5), (20.0, 6), (22.0, 7), (24.0, 8),
  (26.0, 9), (28.0,10), (30.0,11), (32.0,12), (34.0,13), (36.0,14), (38.0,15), (40.0,16),
  (40.0, 1), (38.0, 2), (36.0, 3), (34.0, 4), (32.0, 5), (30.0, 6), (28.0, 7), (26.0, 8),
  (24.0, 9), (22.0,10), (20.0,11), (18.0,12), (16.0,13), (14.0,14), (12.0,15), (10.0,16);
 
-- ================
-- Vidage de `care`
-- ================
 
select * from `care`;
 
-- ===========
-- Requête N°5
-- ===========
 
update      `patient_user` as x
inner join  (    select  patient_user_id,
                         sum(care_amount) as debit
                   from  `care`
               group by  patient_user_id
            ) as c
        on  c.patient_user_id = x.id
 
       set  x.amount = x.amount - c.debit;
 
-- ========================
-- Vidage de `patient_user`
-- ========================
 
select * from `patient_user`;
 
-- ===========
-- Requête N°6
-- ===========
 
select       u.user_lib,
             p.patient_lib,
             sum(c.care_amount)  as debit,
             any_value(x.amount) as total
 
      from  `care`         as c
 
inner join  `patient_user` as x
        on  x.id         = c.patient_user_id
 
inner join  `user`         as u
        on  u.user_id    = x.user_id
 
inner join  `patient`      as p
        on  p.patient_id = x.patient_id
 
  group by  u.user_id, p.patient_id
  order by  u.user_id, p.patient_id;
 
-- ===============
-- Table `payment`
-- ===============
 
DROP TABLE IF EXISTS `payment`;
 
CREATE TABLE `payment`
( `payment_id`        integer unsigned not null auto_increment primary key,
  `payment_amount`    decimal(15,2)    not null,
  `patient_user_id`   integer unsigned not null,
  CONSTRAINT `fk_payment_patient_user` FOREIGN KEY (`patient_user_id`) REFERENCES `patient_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- ========================
-- Insertion dans `payment`
-- ========================
 
insert into `payment` (`payment_amount`,`patient_user_id`) values
  (12.0, 1), (25.0, 2), (40.0, 3), (18.0, 4), (34.0, 5), (21.0, 6), (40.0, 7), (11.0, 8),
  (44.0, 9), (16.0,10), (23.0,11), (26.0,12), (15.0,13), (31.0,14), (35.0,15), (38.0,16),
  (38.0, 1), (25.0, 2), (10.0, 3), (32.0, 4), (16.0, 5), (29.0, 6), ( 0.0, 7), (39.0, 8),
  ( 6.0, 9), (34.0,10), (17.0,11), (24.0,12), (35.0,13), (19.0,14), (15.0,15), (12.0,16);
 
-- ===================
-- Vidage de `payment`
-- ===================
 
select * from `payment`;
 
-- ===========
-- Requête N°7
-- ===========
 
update      `patient_user` as x
inner join  (    select  patient_user_id,
                         sum(payment_amount) as credit
                   from  `payment`
               group by  patient_user_id
            ) as p
        on  p.patient_user_id = x.id
 
       set  x.amount = x.amount + p.credit;
 
-- ========================
-- Vidage de `patient_user`
-- ========================
 
select * from `patient_user`;
 
-- ===========
-- Requête N°8
-- ===========
 
select       u.user_lib,
             p.patient_lib,
             sum(z.payment_amount)  as credit,
             any_value(x.amount)    as total
 
      from  `payment`      as z
 
inner join  `patient_user` as x
        on  x.id         = z.patient_user_id
 
inner join  `user`         as u
        on  u.user_id    = x.user_id
 
inner join  `patient`      as p
        on  p.patient_id = x.patient_id
 
  group by  u.user_id, p.patient_id
  order by  u.user_id, p.patient_id;
 
-- ===========================
-- Requête N°9 : récapitulatif
-- ===========================
 
drop view if exists `vue_racap`;
 
create view `vue_recap` as
select       u.user_lib,
             p.patient_lib,
             t1.credit,
             t2.debit,
            (t2.debit - t1.credit) as diff,
             x.amount      as total
 
      from  `patient_user` as x
 
inner join  `user`         as u
        on  u.user_id    = x.user_id
 
inner join  `patient`      as p
        on  p.patient_id = x.patient_id
 
inner join  (  select  patient_user_id,
                       sum(care_amount) as credit
                 from  `care`
             group by  patient_user_id
            ) as t1
        on  t1.patient_user_id = x.id
 
inner join  (  select  patient_user_id,
                       sum(payment_amount) as debit
                 from  `payment`
             group by  patient_user_id
            ) as t2
        on  t2.patient_user_id = x.id
 
  order by  x.id;
 
select * from vue_recap;
 
-- ====================================
-- Requête N°10 : récapitulatif patient
-- ====================================
 
drop view if exists `vue_patient`;
 
create view `vue_patient` as
select      p.patient_lib,
            t1.total,
            t2.credit,
            t3.debit,
            (t3.debit - t2.credit) as diff
 
      from  `patient` as p
 
inner join  (    select  patient_id,
                         sum(amount) as total
                   from  `patient_user`
               group by  patient_id
            ) as t1
        on  t1.patient_id = p.patient_id
 
inner join  (    select  x1.patient_id,
                         sum(c.care_amount) as credit
                   from  `care`             as c
 
             inner join  `patient_user`     as x1
                     on  x1.id = c.patient_user_id
 
               group by  x1.patient_id
            ) as t2
        on  t2.patient_id = p.patient_id
 
inner join  (    select  x2.patient_id,
                         sum(t.payment_amount) as debit
                   from  `payment`             as t
 
             inner join  `patient_user`      as x2
                     on  x2.id = t.patient_user_id
 
               group by  x2.patient_id
            ) as t3
        on  t3.patient_id = p.patient_id
 
  group by  p.patient_id;
 
select * from vue_patient;
 
select * from vue_patient where total <> 0;
 
-- =================================
-- Requête N°11 : récapitulatif user
-- =================================
 
drop view if exists `vue_user`;
 
create view `vue_user` as
select      u.user_lib,
            t1.total,
            t2.credit,
            t3.debit,
            (t3.debit - t2.credit) as diff
 
      from  `user` as u
 
inner join  (    select  user_id,
                         sum(amount) as total
                   from  `patient_user`
               group by  user_id
            ) as t1
        on  t1.user_id = u.user_id
 
inner join  (    select  x1.user_id,
                         sum(c.care_amount) as credit
                   from  `care`             as c
 
             inner join  `patient_user`     as x1
                     on  x1.id = c.patient_user_id
 
               group by  x1.user_id
            ) as t2
        on  t2.user_id = u.user_id
 
inner join  (    select  x2.user_id,
                         sum(t.payment_amount) as debit
                   from  `payment`             as t
 
             inner join  `patient_user`      as x2
                     on  x2.id = t.patient_user_id
 
               group by  x2.user_id
            ) as t3
        on  t3.user_id = u.user_id
 
  group by  u.user_id;
 
select * from vue_user;
 
select * from vue_user where total <> 0;
 
-- ===
-- Fin
-- ===
 
COMMIT;
SET AUTOCOMMIT = 1;
exit

Ainsi vous pourrez reprendre mon exemple afin de l'adapter !

@+