Discussion :

[CaptainWyatt]

Bonjour

Mon niveau de compétences en MySQL ne me permet pas de répondre à ce problème, sans doute très simple pour un expertr

J'ai une table 'A' composée ainsi
identifiant_film (int), producteur (text), assistant (text)

dans une table 'B' j'ai
identifiant_film (int), nom (text), fonction (text)

où j'ai bein entendu plusieurs noms et fonctions pour le même identifiant.
je veux ventiler dans la table 'A' une concatenation de tous les noms de la table 'B'
dans la colonne 'producteur' tous le noms dont la fonction dans la table 'B' est 'producteur'
dans le colonne 'assistant' tous le noms dont la fonction dans la table 'B' est 'assistant'

je veux pour cela utiliser la fonction GROUP_CONCAT avec soit CREATE TABLE A ou INSERT INTO TABLE A ou UPDATE TABLE A FROM TABLE B.

je veux pouvoir écrire
SI 'fonction' = 'producteur' concaténer dans colonne 'A.producteur'
sinon
SI 'fonction' = 'assistant' concaténer dans colonne 'A.assistant'
sinon
ne rien faire

Merci de votre aide

[Artemus24]

Salut CaptainWyatt.

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
--------------
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 `tabone`
--------------
 
--------------
CREATE TABLE `tabone`
( `identifiant_film`   integer unsigned not null auto_increment primary key,
  `producteur`         text             not null,
  `assistant`          text             not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tabone` (`identifiant_film`,`producteur`,`assistant`) values
  (1, 'bla bla', 'bla bla')
--------------
 
--------------
select * from tabone
--------------
 
+------------------+------------+-----------+
| identifiant_film | producteur | assistant |
+------------------+------------+-----------+
|                1 | bla bla    | bla bla   |
+------------------+------------+-----------+
--------------
DROP TABLE IF EXISTS `tabtwo`
--------------
 
--------------
CREATE TABLE `tabtwo`
( `id`                 integer unsigned not null auto_increment primary key,
  `identifiant_film`   integer unsigned not null,
  `nom`                text             not null,
  `fonction`           text             not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tabtwo` (`identifiant_film`,`nom`,`fonction`) values
  (1, 'prod_1_1', 'producteur'),(1, 'prod_1_2',   'producteur'),(1, 'prod_1_3',   'producteur'),(1, 'assist_1_1', 'assistant'),(1, 'assist_1_2', 'assistant'),
  (2, 'prod_2_1', 'producteur'),(2, 'assist_2_1', 'assistant'), (2, 'assist_2_2', 'assistant'), (2, 'assist_2_3', 'assistant'),
  (3, 'prod_3_1', 'producteur'),(3, 'prod_3_2',   'producteur'),(3, 'assist_3_1', 'assistant')
--------------
 
--------------
select * from tabtwo
--------------
 
+----+------------------+------------+------------+
| id | identifiant_film | nom        | fonction   |
+----+------------------+------------+------------+
|  1 |                1 | prod_1_1   | producteur |
|  2 |                1 | prod_1_2   | producteur |
|  3 |                1 | prod_1_3   | producteur |
|  4 |                1 | assist_1_1 | assistant  |
|  5 |                1 | assist_1_2 | assistant  |
|  6 |                2 | prod_2_1   | producteur |
|  7 |                2 | assist_2_1 | assistant  |
|  8 |                2 | assist_2_2 | assistant  |
|  9 |                2 | assist_2_3 | assistant  |
| 10 |                3 | prod_3_1   | producteur |
| 11 |                3 | prod_3_2   | producteur |
| 12 |                3 | assist_3_1 | assistant  |
+----+------------------+------------+------------+
--------------
insert   into `tabone` (`identifiant_film`,`producteur`,`assistant`)
select    t1.identifiant_film,
         (select group_concat(t2.nom separator '+') from tabtwo as t2 where t2.identifiant_film = t1.identifiant_film and fonction like 'producteur' group by identifiant_film, fonction) as producteur,
         (select group_concat(t3.nom separator '+') from tabtwo as t3 where t3.identifiant_film = t1.identifiant_film and fonction like 'assistant'  group by identifiant_film, fonction) as assistant
    from  tabtwo as t1
group by  identifiant_film
on duplicate key update producteur = values(`producteur`), assistant = values(`assistant`)
--------------
 
--------------
select * from tabone
--------------
 
+------------------+----------------------------+----------------------------------+
| identifiant_film | producteur                 | assistant                        |
+------------------+----------------------------+----------------------------------+
|                1 | prod_1_1+prod_1_2+prod_1_3 | assist_1_1+assist_1_2            |
|                2 | prod_2_1                   | assist_2_1+assist_2_2+assist_2_3 |
|                3 | prod_3_1+prod_3_2          | assist_3_1                       |
+------------------+----------------------------+----------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

[CaptainWyatt]

Salut CaptainWyatt.

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
--------------
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 `tabone`
--------------
 
--------------
CREATE TABLE `tabone`
( `identifiant_film`   integer unsigned not null auto_increment primary key,
  `producteur`         text             not null,
  `assistant`          text             not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tabone` (`identifiant_film`,`producteur`,`assistant`) values
  (1, 'bla bla', 'bla bla')
--------------
 
--------------
select * from tabone
--------------
 
+------------------+------------+-----------+
| identifiant_film | producteur | assistant |
+------------------+------------+-----------+
|                1 | bla bla    | bla bla   |
+------------------+------------+-----------+
--------------
DROP TABLE IF EXISTS `tabtwo`
--------------
 
--------------
CREATE TABLE `tabtwo`
( `id`                 integer unsigned not null auto_increment primary key,
  `identifiant_film`   integer unsigned not null,
  `nom`                text             not null,
  `fonction`           text             not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tabtwo` (`identifiant_film`,`nom`,`fonction`) values
  (1, 'prod_1_1', 'producteur'),(1, 'prod_1_2',   'producteur'),(1, 'prod_1_3',   'producteur'),(1, 'assist_1_1', 'assistant'),(1, 'assist_1_2', 'assistant'),
  (2, 'prod_2_1', 'producteur'),(2, 'assist_2_1', 'assistant'), (2, 'assist_2_2', 'assistant'), (2, 'assist_2_3', 'assistant'),
  (3, 'prod_3_1', 'producteur'),(3, 'prod_3_2',   'producteur'),(3, 'assist_3_1', 'assistant')
--------------
 
--------------
select * from tabtwo
--------------
 
+----+------------------+------------+------------+
| id | identifiant_film | nom        | fonction   |
+----+------------------+------------+------------+
|  1 |                1 | prod_1_1   | producteur |
|  2 |                1 | prod_1_2   | producteur |
|  3 |                1 | prod_1_3   | producteur |
|  4 |                1 | assist_1_1 | assistant  |
|  5 |                1 | assist_1_2 | assistant  |
|  6 |                2 | prod_2_1   | producteur |
|  7 |                2 | assist_2_1 | assistant  |
|  8 |                2 | assist_2_2 | assistant  |
|  9 |                2 | assist_2_3 | assistant  |
| 10 |                3 | prod_3_1   | producteur |
| 11 |                3 | prod_3_2   | producteur |
| 12 |                3 | assist_3_1 | assistant  |
+----+------------------+------------+------------+
--------------
insert   into `tabone` (`identifiant_film`,`producteur`,`assistant`)
select    t1.identifiant_film,
         (select group_concat(t2.nom separator '+') from tabtwo as t2 where t2.identifiant_film = t1.identifiant_film and fonction like 'producteur' group by identifiant_film, fonction) as producteur,
         (select group_concat(t3.nom separator '+') from tabtwo as t3 where t3.identifiant_film = t1.identifiant_film and fonction like 'assistant'  group by identifiant_film, fonction) as assistant
    from  tabtwo as t1
group by  identifiant_film
on duplicate key update producteur = values(`producteur`), assistant = values(`assistant`)
--------------
 
--------------
select * from tabone
--------------
 
+------------------+----------------------------+----------------------------------+
| identifiant_film | producteur                 | assistant                        |
+------------------+----------------------------+----------------------------------+
|                1 | prod_1_1+prod_1_2+prod_1_3 | assist_1_1+assist_1_2            |
|                2 | prod_2_1                   | assist_2_1+assist_2_2+assist_2_3 |
|                3 | prod_3_1+prod_3_2          | assist_3_1                       |
+------------------+----------------------------+----------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

[CaptainWyatt]

bonjour
voilà l'adaptation que j'ai faite
table auteurs

Code :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE `auteurs` (
  `id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `filmaut` INTEGER(10) 
  `scenario` TEXT 
  `adaptation` TEXT 
  `dapres` TEXT 
  PRIMARY KEY USING BTREE (`id`)
) 
;

Table masse

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE `masse` (
  `id` INTEGER(10) 
  `filmaut` INTEGER(10) 
  `nom` TEXT,
  `addition` TEXT 
  PRIMARY KEY USING BTREE (`id`)
) ENGINE=InnoDB
;

requete

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
insert   into auteurs (filmaut,scenario,adaptation, dapres)
select    t1.filmaut,
         (select group_concat(t2.nom separator '+') from masse as t2 where t2.filmaut = t1.filmaut
          and addition like '%(Screenplay%' group by filmaut, nom) as scenario,
         (select group_concat(t3.nom separator '+') from masse as t3 where t3.filmaut = t1.filmaut 
         and addition  not like '%(Screenplay%'  group by filmaut, nom) as adaptation,
          (select addition from masse as t4 where t4.filmaut = t1.filmaut 
         and addition  not like '%(Screenplay%') as dapres
    from  masse as t1
 
group by  filmaut
 
on duplicate key update scenario = values(scenario), adaptation = values(adaptation)

ceci me retourne systématiquement:

Subquery returns more than 1 row

où est l'erreur?

Merci d'avance

[Artemus24]

Salut CaptainWyatt.

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
--------------
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 `auteurs`
--------------
 
--------------
CREATE TABLE `auteurs`
( `filmaut`     integer not null primary key,
  `scenario`    text        null,
  `adaptation`  text        null,
  `dapres`      text        null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
select * from auteurs
--------------
 
--------------
DROP TABLE IF EXISTS `masse`
--------------
 
--------------
CREATE TABLE `masse`
( `id`          integer unsigned not null auto_increment primary key,
  `filmaut`     integer          not null,
  `nom`         text             not null,
  `addition`    text             not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `masse` (`filmaut`,`nom`,`addition`) values
  (1, 'scenario 01', '(screenplay 01'),
  (2, 'scenario 02', 'addition 02'),
  (3, 'scenario 03', 'addition 03'),
  (4, 'scenario 04', '(screenplay 04'),
  (5, 'scenario 05', 'addition 05'),
  (1, 'scenario 06', 'addition 11'),
  (2, 'scenario 07', '(screenplay 12'),
  (3, 'scenario 08', 'addition 13'),
  (4, 'scenario 09', 'addition 14'),
  (5, 'scenario 10', '(screenplay 15'),
 
  (1, 'scenario 01', 'addition 21'),
  (2, 'scenario 02', 'addition 22'),
  (3, 'scenario 03', '(screenplay 23'),
  (4, 'scenario 04', 'addition 24'),
  (5, 'scenario 05', 'addition 25'),
  (1, 'scenario 06', '(screenplay 31'),
  (2, 'scenario 07', 'addition 32'),
  (3, 'scenario 08', 'addition 33'),
  (4, 'scenario 09', '(screenplay 34'),
  (5, 'scenario 10', 'addition 35'),
 
  (1, 'scenario 01', 'addition 41'),
  (2, 'scenario 02', '(screenplay 42'),
  (3, 'scenario 03', 'addition 43'),
  (4, 'scenario 04', 'addition 44'),
  (5, 'scenario 05', '(screenplay 45'),
  (1, 'scenario 06', 'addition 51'),
  (2, 'scenario 07', 'addition 52'),
  (3, 'scenario 08', '(screenplay 53'),
  (4, 'scenario 09', 'addition 54'),
  (5, 'scenario 10', 'addition 55')
--------------
 
--------------
select * from masse
--------------
 
+----+---------+-------------+----------------+
| id | filmaut | nom         | addition       |
+----+---------+-------------+----------------+
|  1 |       1 | scenario 01 | (screenplay 01 |
|  2 |       2 | scenario 02 | addition 02    |
|  3 |       3 | scenario 03 | addition 03    |
|  4 |       4 | scenario 04 | (screenplay 04 |
|  5 |       5 | scenario 05 | addition 05    |
|  6 |       1 | scenario 06 | addition 11    |
|  7 |       2 | scenario 07 | (screenplay 12 |
|  8 |       3 | scenario 08 | addition 13    |
|  9 |       4 | scenario 09 | addition 14    |
| 10 |       5 | scenario 10 | (screenplay 15 |
| 11 |       1 | scenario 01 | addition 21    |
| 12 |       2 | scenario 02 | addition 22    |
| 13 |       3 | scenario 03 | (screenplay 23 |
| 14 |       4 | scenario 04 | addition 24    |
| 15 |       5 | scenario 05 | addition 25    |
| 16 |       1 | scenario 06 | (screenplay 31 |
| 17 |       2 | scenario 07 | addition 32    |
| 18 |       3 | scenario 08 | addition 33    |
| 19 |       4 | scenario 09 | (screenplay 34 |
| 20 |       5 | scenario 10 | addition 35    |
| 21 |       1 | scenario 01 | addition 41    |
| 22 |       2 | scenario 02 | (screenplay 42 |
| 23 |       3 | scenario 03 | addition 43    |
| 24 |       4 | scenario 04 | addition 44    |
| 25 |       5 | scenario 05 | (screenplay 45 |
| 26 |       1 | scenario 06 | addition 51    |
| 27 |       2 | scenario 07 | addition 52    |
| 28 |       3 | scenario 08 | (screenplay 53 |
| 29 |       4 | scenario 09 | addition 54    |
| 30 |       5 | scenario 10 | addition 55    |
+----+---------+-------------+----------------+
--------------
insert into `auteurs` (`filmaut`,`scenario`)
 
  select    filmaut,
            group_concat(distinct t2.nom  separator ' + ') as scenario
      from  masse as t2
     where  addition like '%(Screenplay%'
  group by  filmaut
 
on duplicate key update scenario = values(`scenario`)
--------------
 
--------------
select * from auteurs
--------------
 
+---------+---------------------------+------------+--------+
| filmaut | scenario                  | adaptation | dapres |
+---------+---------------------------+------------+--------+
|       1 | scenario 01 + scenario 06 | NULL       | NULL   |
|       2 | scenario 07 + scenario 02 | NULL       | NULL   |
|       3 | scenario 03 + scenario 08 | NULL       | NULL   |
|       4 | scenario 04 + scenario 09 | NULL       | NULL   |
|       5 | scenario 10 + scenario 05 | NULL       | NULL   |
+---------+---------------------------+------------+--------+
--------------
insert into `auteurs` (`filmaut`,`adaptation`)
 
  select    filmaut,
            group_concat(distinct t3.nom separator ' + ') as adaptation
      from  masse as t3
     where  addition not like '%(Screenplay%'
  group by  filmaut
 
on duplicate key update adaptation = values(`adaptation`)
--------------
 
--------------
select * from auteurs
--------------
 
+---------+---------------------------+---------------------------+--------+
| filmaut | scenario                  | adaptation                | dapres |
+---------+---------------------------+---------------------------+--------+
|       1 | scenario 01 + scenario 06 | scenario 06 + scenario 01 | NULL   |
|       2 | scenario 07 + scenario 02 | scenario 02 + scenario 07 | NULL   |
|       3 | scenario 03 + scenario 08 | scenario 03 + scenario 08 | NULL   |
|       4 | scenario 04 + scenario 09 | scenario 09 + scenario 04 | NULL   |
|       5 | scenario 10 + scenario 05 | scenario 05 + scenario 10 | NULL   |
+---------+---------------------------+---------------------------+--------+
--------------
insert into `auteurs` (`filmaut`,`dapres`)
 
  select    filmaut,
            group_concat(distinct t4.addition separator ' + ') as dapres
      from  masse as t4
     where  addition not like '%(Screenplay%'
  group by  filmaut
 
on duplicate key update dapres = values(`dapres`)
--------------
 
--------------
select * from auteurs
--------------
 
+---------+---------------------------+---------------------------+-------------------------------------------------------+
| filmaut | scenario                  | adaptation                | dapres                                                |
+---------+---------------------------+---------------------------+-------------------------------------------------------+
|       1 | scenario 01 + scenario 06 | scenario 06 + scenario 01 | addition 11 + addition 21 + addition 41 + addition 51 |
|       2 | scenario 07 + scenario 02 | scenario 02 + scenario 07 | addition 02 + addition 22 + addition 32 + addition 52 |
|       3 | scenario 03 + scenario 08 | scenario 03 + scenario 08 | addition 03 + addition 13 + addition 33 + addition 43 |
|       4 | scenario 04 + scenario 09 | scenario 09 + scenario 04 | addition 14 + addition 24 + addition 44 + addition 54 |
|       5 | scenario 10 + scenario 05 | scenario 05 + scenario 10 | addition 05 + addition 25 + addition 35 + addition 55 |
+---------+---------------------------+---------------------------+-------------------------------------------------------+
--------------
truncate auteurs
--------------
 
--------------
select * from auteurs
--------------
 
--------------
insert into `auteurs` (`filmaut`,`scenario`,`adaptation`,`dapres`)
select t1.filmaut,
 
       (select  group_concat(distinct t2.nom separator ' + ')
          from  masse as t2
         where  t2.filmaut = t1.filmaut
           and  t2.addition like '%(Screenplay%') as scenario,
 
       (select  group_concat(distinct t3.nom separator ' + ')
          from  masse as t3
         where  t3.filmaut = t1.filmaut
           and  t3.addition not like '%(Screenplay%') as adaptation,
 
       (select  group_concat(distinct t4.addition separator ' + ')
          from  masse as t4
         where  t4.filmaut = t1.filmaut
           and  t4.addition not like '%(Screenplay%') as dapres
 
from masse as t1
 
group by filmaut
 
on duplicate key update scenario   = values(`scenario`),
                        adaptation = values(`adaptation`),
                        dapres     = values(`dapres`)
--------------
 
--------------
select * from auteurs
--------------
 
+---------+---------------------------+---------------------------+-------------------------------------------------------+
| filmaut | scenario                  | adaptation                | dapres                                                |
+---------+---------------------------+---------------------------+-------------------------------------------------------+
|       1 | scenario 01 + scenario 06 | scenario 06 + scenario 01 | addition 11 + addition 21 + addition 41 + addition 51 |
|       2 | scenario 07 + scenario 02 | scenario 02 + scenario 07 | addition 02 + addition 22 + addition 32 + addition 52 |
|       3 | scenario 03 + scenario 08 | scenario 03 + scenario 08 | addition 03 + addition 13 + addition 33 + addition 43 |
|       4 | scenario 04 + scenario 09 | scenario 09 + scenario 04 | addition 14 + addition 24 + addition 44 + addition 54 |
|       5 | scenario 10 + scenario 05 | scenario 05 + scenario 10 | addition 05 + addition 25 + addition 35 + addition 55 |
+---------+---------------------------+---------------------------+-------------------------------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

[CaptainWyatt]

Salut CaptainWyatt.

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
--------------
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 `auteurs`
--------------
 
--------------
CREATE TABLE `auteurs`
( `filmaut`     integer not null primary key,
  `scenario`    text        null,
  `adaptation`  text        null,
  `dapres`      text        null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
select * from auteurs
--------------
 
--------------
DROP TABLE IF EXISTS `masse`
--------------
 
--------------
CREATE TABLE `masse`
( `id`          integer unsigned not null auto_increment primary key,
  `filmaut`     integer          not null,
  `nom`         text             not null,
  `addition`    text             not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `masse` (`filmaut`,`nom`,`addition`) values
  (1, 'scenario 01', '(screenplay 01'),
  (2, 'scenario 02', 'addition 02'),
  (3, 'scenario 03', 'addition 03'),
  (4, 'scenario 04', '(screenplay 04'),
  (5, 'scenario 05', 'addition 05'),
  (1, 'scenario 06', 'addition 11'),
  (2, 'scenario 07', '(screenplay 12'),
  (3, 'scenario 08', 'addition 13'),
  (4, 'scenario 09', 'addition 14'),
  (5, 'scenario 10', '(screenplay 15'),
 
  (1, 'scenario 01', 'addition 21'),
  (2, 'scenario 02', 'addition 22'),
  (3, 'scenario 03', '(screenplay 23'),
  (4, 'scenario 04', 'addition 24'),
  (5, 'scenario 05', 'addition 25'),
  (1, 'scenario 06', '(screenplay 31'),
  (2, 'scenario 07', 'addition 32'),
  (3, 'scenario 08', 'addition 33'),
  (4, 'scenario 09', '(screenplay 34'),
  (5, 'scenario 10', 'addition 35'),
 
  (1, 'scenario 01', 'addition 41'),
  (2, 'scenario 02', '(screenplay 42'),
  (3, 'scenario 03', 'addition 43'),
  (4, 'scenario 04', 'addition 44'),
  (5, 'scenario 05', '(screenplay 45'),
  (1, 'scenario 06', 'addition 51'),
  (2, 'scenario 07', 'addition 52'),
  (3, 'scenario 08', '(screenplay 53'),
  (4, 'scenario 09', 'addition 54'),
  (5, 'scenario 10', 'addition 55')
--------------
 
--------------
select * from masse
--------------
 
+----+---------+-------------+----------------+
| id | filmaut | nom         | addition       |
+----+---------+-------------+----------------+
|  1 |       1 | scenario 01 | (screenplay 01 |
|  2 |       2 | scenario 02 | addition 02    |
|  3 |       3 | scenario 03 | addition 03    |
|  4 |       4 | scenario 04 | (screenplay 04 |
|  5 |       5 | scenario 05 | addition 05    |
|  6 |       1 | scenario 06 | addition 11    |
|  7 |       2 | scenario 07 | (screenplay 12 |
|  8 |       3 | scenario 08 | addition 13    |
|  9 |       4 | scenario 09 | addition 14    |
| 10 |       5 | scenario 10 | (screenplay 15 |
| 11 |       1 | scenario 01 | addition 21    |
| 12 |       2 | scenario 02 | addition 22    |
| 13 |       3 | scenario 03 | (screenplay 23 |
| 14 |       4 | scenario 04 | addition 24    |
| 15 |       5 | scenario 05 | addition 25    |
| 16 |       1 | scenario 06 | (screenplay 31 |
| 17 |       2 | scenario 07 | addition 32    |
| 18 |       3 | scenario 08 | addition 33    |
| 19 |       4 | scenario 09 | (screenplay 34 |
| 20 |       5 | scenario 10 | addition 35    |
| 21 |       1 | scenario 01 | addition 41    |
| 22 |       2 | scenario 02 | (screenplay 42 |
| 23 |       3 | scenario 03 | addition 43    |
| 24 |       4 | scenario 04 | addition 44    |
| 25 |       5 | scenario 05 | (screenplay 45 |
| 26 |       1 | scenario 06 | addition 51    |
| 27 |       2 | scenario 07 | addition 52    |
| 28 |       3 | scenario 08 | (screenplay 53 |
| 29 |       4 | scenario 09 | addition 54    |
| 30 |       5 | scenario 10 | addition 55    |
+----+---------+-------------+----------------+
--------------
insert into `auteurs` (`filmaut`,`scenario`)
 
  select    filmaut,
            group_concat(distinct t2.nom  separator ' + ') as scenario
      from  masse as t2
     where  addition like '%(Screenplay%'
  group by  filmaut
 
on duplicate key update scenario = values(`scenario`)
--------------
 
--------------
select * from auteurs
--------------
 
+---------+---------------------------+------------+--------+
| filmaut | scenario                  | adaptation | dapres |
+---------+---------------------------+------------+--------+
|       1 | scenario 01 + scenario 06 | NULL       | NULL   |
|       2 | scenario 07 + scenario 02 | NULL       | NULL   |
|       3 | scenario 03 + scenario 08 | NULL       | NULL   |
|       4 | scenario 04 + scenario 09 | NULL       | NULL   |
|       5 | scenario 10 + scenario 05 | NULL       | NULL   |
+---------+---------------------------+------------+--------+
--------------
insert into `auteurs` (`filmaut`,`adaptation`)
 
  select    filmaut,
            group_concat(distinct t3.nom separator ' + ') as adaptation
      from  masse as t3
     where  addition not like '%(Screenplay%'
  group by  filmaut
 
on duplicate key update adaptation = values(`adaptation`)
--------------
 
--------------
select * from auteurs
--------------
 
+---------+---------------------------+---------------------------+--------+
| filmaut | scenario                  | adaptation                | dapres |
+---------+---------------------------+---------------------------+--------+
|       1 | scenario 01 + scenario 06 | scenario 06 + scenario 01 | NULL   |
|       2 | scenario 07 + scenario 02 | scenario 02 + scenario 07 | NULL   |
|       3 | scenario 03 + scenario 08 | scenario 03 + scenario 08 | NULL   |
|       4 | scenario 04 + scenario 09 | scenario 09 + scenario 04 | NULL   |
|       5 | scenario 10 + scenario 05 | scenario 05 + scenario 10 | NULL   |
+---------+---------------------------+---------------------------+--------+
--------------
insert into `auteurs` (`filmaut`,`dapres`)
 
  select    filmaut,
            group_concat(distinct t4.addition separator ' + ') as dapres
      from  masse as t4
     where  addition not like '%(Screenplay%'
  group by  filmaut
 
on duplicate key update dapres = values(`dapres`)
--------------
 
--------------
select * from auteurs
--------------
 
+---------+---------------------------+---------------------------+-------------------------------------------------------+
| filmaut | scenario                  | adaptation                | dapres                                                |
+---------+---------------------------+---------------------------+-------------------------------------------------------+
|       1 | scenario 01 + scenario 06 | scenario 06 + scenario 01 | addition 11 + addition 21 + addition 41 + addition 51 |
|       2 | scenario 07 + scenario 02 | scenario 02 + scenario 07 | addition 02 + addition 22 + addition 32 + addition 52 |
|       3 | scenario 03 + scenario 08 | scenario 03 + scenario 08 | addition 03 + addition 13 + addition 33 + addition 43 |
|       4 | scenario 04 + scenario 09 | scenario 09 + scenario 04 | addition 14 + addition 24 + addition 44 + addition 54 |
|       5 | scenario 10 + scenario 05 | scenario 05 + scenario 10 | addition 05 + addition 25 + addition 35 + addition 55 |
+---------+---------------------------+---------------------------+-------------------------------------------------------+
--------------
truncate auteurs
--------------
 
--------------
select * from auteurs
--------------
 
--------------
insert into `auteurs` (`filmaut`,`scenario`,`adaptation`,`dapres`)
select t1.filmaut,
 
       (select  group_concat(distinct t2.nom separator ' + ')
          from  masse as t2
         where  t2.filmaut = t1.filmaut
           and  t2.addition like '%(Screenplay%') as scenario,
 
       (select  group_concat(distinct t3.nom separator ' + ')
          from  masse as t3
         where  t3.filmaut = t1.filmaut
           and  t3.addition not like '%(Screenplay%') as adaptation,
 
       (select  group_concat(distinct t4.addition separator ' + ')
          from  masse as t4
         where  t4.filmaut = t1.filmaut
           and  t4.addition not like '%(Screenplay%') as dapres
 
from masse as t1
 
group by filmaut
 
on duplicate key update scenario   = values(`scenario`),
                        adaptation = values(`adaptation`),
                        dapres     = values(`dapres`)
--------------
 
--------------
select * from auteurs
--------------
 
+---------+---------------------------+---------------------------+-------------------------------------------------------+
| filmaut | scenario                  | adaptation                | dapres                                                |
+---------+---------------------------+---------------------------+-------------------------------------------------------+
|       1 | scenario 01 + scenario 06 | scenario 06 + scenario 01 | addition 11 + addition 21 + addition 41 + addition 51 |
|       2 | scenario 07 + scenario 02 | scenario 02 + scenario 07 | addition 02 + addition 22 + addition 32 + addition 52 |
|       3 | scenario 03 + scenario 08 | scenario 03 + scenario 08 | addition 03 + addition 13 + addition 33 + addition 43 |
|       4 | scenario 04 + scenario 09 | scenario 09 + scenario 04 | addition 14 + addition 24 + addition 44 + addition 54 |
|       5 | scenario 10 + scenario 05 | scenario 05 + scenario 10 | addition 05 + addition 25 + addition 35 + addition 55 |
+---------+---------------------------+---------------------------+-------------------------------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

[Artemus24]

Salut Captain Wyatt.

Si tu veux normaliser ta requête, il vaut mieux utiliser les jointures comme ci-après :

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
--------------
truncate auteurs
--------------
 
--------------
select * from auteurs
--------------
 
--------------
insert into `auteurs` (`filmaut`,`scenario`,`adaptation`,`dapres`)
 
select                                 t1.filmaut,
                 group_concat(distinct t2.nom      separator ' + '),
                 group_concat(distinct t3.nom      separator ' + '),
                 group_concat(distinct t4.addition separator ' + ')
 
           from  masse as t1
 
left outer join  masse as t2
             on  t2.filmaut = t1.filmaut
            and  t2.addition     like '%(Screenplay%'
 
left outer join  masse as t3
             on  t3.filmaut = t1.filmaut
            and  t3.addition not like '%(Screenplay%'
 
left outer join  masse as t4
             on  t4.filmaut = t1.filmaut
           and   t4.addition not like '%(Screenplay%'
 
group by t1.filmaut
 
on duplicate key update scenario   = values(`scenario`),
                        adaptation = values(`adaptation`),
                        dapres     = values(`dapres`)
--------------
 
--------------
select * from auteurs
--------------
 
+---------+---------------------------+---------------------------+-------------------------------------------------------+
| filmaut | scenario                  | adaptation                | dapres                                                |
+---------+---------------------------+---------------------------+-------------------------------------------------------+
|       1 | scenario 01 + scenario 06 | scenario 06 + scenario 01 | addition 11 + addition 21 + addition 41 + addition 51 |
|       2 | scenario 07 + scenario 02 | scenario 02 + scenario 07 | addition 02 + addition 22 + addition 32 + addition 52 |
|       3 | scenario 03 + scenario 08 | scenario 03 + scenario 08 | addition 03 + addition 13 + addition 33 + addition 43 |
|       4 | scenario 04 + scenario 09 | scenario 09 + scenario 04 | addition 14 + addition 24 + addition 44 + addition 54 |
|       5 | scenario 10 + scenario 05 | scenario 05 + scenario 10 | addition 05 + addition 25 + addition 35 + addition 55 |
+---------+---------------------------+---------------------------+-------------------------------------------------------+

@+