Agréger des données

**lepatantpato** · 27/12/2017, 13h22

Bonjour

J'ai une table1 ainsi :

id	Pays	p1	p2	p3
1	France	1	3
2	Angleterre	2	1	5
etc...

J'ai une table2 :

1	Pierre
2	Paul
3	Jacques
4	Daniel
5	Pierre

La table1 contient des colonnes de 1 à 3 contenant des identifiants de table2, p1, p2 et p3, sachant que ces rubriques peuvent être vides, remplies partiellement ou pas...

Je désire afficher la liste des pays avec les noms équivalents et obtenir ceci :

France Pierre Jacques
Angleterre Paul Pierre Jean
etc...
Quelqu'un saurait me donner une piste pour une requêt en MySql ?
Merci !

**vttman** · 27/12/2017, 18h06

Tester de genre de requêtes avec alias de table et jointures externes

Code :

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

1
2
3
4
5
6
select t.pays,t21.nom,t22.nom, ...  from table1 t 
left join table2 t21 on
t.p1 = t21.id
left join table2 t22 on
t.p2 = t22.id
left join ...

http://sqlpro.developpez.com/cours/sqlaz/jointures/

**escartefigue** · 27/12/2017, 20h39

Bonsoir;

Votre base de données est très mal modélisée, vous avez répété l'identifiant personne dans la table des pays pour savoir dans quel pays se situe une personne
Ce type de modélisation est acceptable dans un tableur, mais certainement pas dans une base relationnelle
Que ferez vous quand une personne sera présente dans plus de 3 pays : vous serez contraint de modifier votre table "table1" pour ajouter autant de colonnes que le nombre requis pour la personne présente dans le plus grand nombre de pays
Et ces colonnes seront inutiles pour les personnes présentes dans un seul pays

Le bonne façon de procéder, au niveau conceptuel, est la suivante :
PERSONNE 0,n --- visiter --- 0,n PAYS
CALENDIER 0,n ------┘

Ce qui donne le modèle logique suivant
TABLE PERSONNE (PE_id, PE_nom, PE_prenom, PE_dtnais, PE_numss...)
TABLE PAYS (PY_id, PY_code, PY_libelle...)
TABLE VISITER (PE_id, PY_id, CL_id...)
TABLE CALENDRIER (CL_id, CL_DATE)

La requête qui permet de savoir quelle personne a visité tel pays à telle date est donc la suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
select PE_NOM
     , PE_PRENOM
     , CL_DATE
     , PY_LIBELLE
from PERSONNE as PE
inner join VISITER as VI
   on VI.PE_id=PE.PE_id)
inner join CALENDRIER as CL
   on CL.CL_id=VI.CL_id
inner join PAYS as PY
   on PY.PY_id=VS.PY_id
order by PE.PE_NOM
       , PE.PE_PRENOM

Note : dans la vraie vie, on génère rarement la table calendrier, mais on ajoute la date dans la PK de la table "visiter" issue de la relation entre les personnes et les pays, de façon à permettre à une personne de visiter un même pays plusieurs fois

**Artemus24** · 28/12/2017, 13h19

Salut à tous.

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
--------------
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 `personne`
--------------
 
--------------
CREATE TABLE `personne`
( `id`    integer unsigned  not null auto_increment primary key,
  `nom`   varchar(255)      not null
  ) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `personne` (`nom`) values
  ('Pierre'),('Paul'),('Jacques'),('Daniel'),('François')
--------------
 
--------------
select * from `personne`
--------------
 
+----+----------+
| id | nom      |
+----+----------+
|  1 | Pierre   |
|  2 | Paul     |
|  3 | Jacques  |
|  4 | Daniel   |
|  5 | François |
+----+----------+
--------------
DROP TABLE IF EXISTS `pays`
--------------
 
--------------
CREATE TABLE `pays`
( `id`       integer unsigned  not null auto_increment primary key,
  `libelle`  varchar(255)      not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `pays` (`libelle`) values
  ('France'),('Angleterre'),('Allemagne'),('Suisse')
--------------
 
--------------
select * from `pays`
--------------
 
+----+------------+
| id | libelle    |
+----+------------+
|  1 | France     |
|  2 | Angleterre |
|  3 | Allemagne  |
|  4 | Suisse     |
+----+------------+
--------------
DROP TABLE IF EXISTS `lien`
--------------
 
--------------
CREATE TABLE `lien`
( `id`             integer unsigned  not null auto_increment primary key,
  `codePays`       integer unsigned  not null,
  `codePersonne`   integer unsigned  not null,
  CONSTRAINT `FK_CODEPAYS`     FOREIGN KEY (`codePays`)     REFERENCES `pays`     (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CODEPERSONNE` FOREIGN KEY (`codePersonne`) REFERENCES `personne` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `lien` (`codePays`,`codepersonne`) values
  (1,1),(1,3),
  (2,2),(2,1),(2,5),
  (3,3),(3,4)
--------------
 
--------------
select * from `lien`
--------------
 
+----+----------+--------------+
| id | codePays | codePersonne |
+----+----------+--------------+
|  1 |        1 |            1 |
|  2 |        1 |            3 |
|  3 |        2 |            2 |
|  4 |        2 |            1 |
|  5 |        2 |            5 |
|  6 |        3 |            3 |
|  7 |        3 |            4 |
+----+----------+--------------+
--------------
select           t2.libelle,
                 group_concat(t3.nom order by t3.nom separator ', ') as nom
           from  `lien`     as t1
 
left outer join  `pays`     as t2
             on  t2.id = t1.codePays
 
left outer join  `personne` as t3
             on  t3.id = t1.codePersonne
 
       group by  t2.libelle
       order by  t2.libelle
--------------
 
+------------+------------------------+
| libelle    | nom                    |
+------------+------------------------+
| Allemagne  | Daniel, Jacques        |
| Angleterre | François, Paul, Pierre |
| France     | Jacques, Pierre        |
+------------+------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

**lepatantpato** · 28/12/2017, 15h53

Bonjour
Merci de vos réponses.

A vttman:
je ne comprends pas ce que fait t21 dans
"left join table2 t21 on"

A Escartefigue
Vous avez raison concernant l'identifiant personne; en fait, dans table1, il n'y a que 3 valeurs max.

A Vttman,Escartefigue et Artemus24
Vraiment merci. Je vais étudier vos codes.

**Michel Rotta** · 28/12/2017, 18h22

Dans la réponse de vttman, le t21 est l'alias de la table.

On ne peut pas ouvrir deux tables qui ont le même nom (ou plutôt deux fois la même table avec le même nom), on va donc utiliser deux alias t21 (pour la première colonne), t22 (pour la deuxième), t23 (pour la troisième) ce qui permet d'ouvrir trois fois la table des nom.

Techniquement la solution d'Artemus24 est la plus propre.

Personnellement je pousserais un peu plus loin l'analyse pour savoir ce que vous mettez derrière l'entête de colonne p1, p2 et p3. Suivant le sens, il peut être justifié d'avoir trois colonnes différentes.

**lepatantpato** · 29/12/2017, 16h20

A Michel Rotta :
J'ai compris pour les alias : merci et du coup, la réponse de Vttman me convient fort bien et fonctionne comme je le désire.
En fait, la table étant constituée ainsi (je reprends un existant) , je n'ai pas d'autres choix que de l'utiliser telle quelle. Les 3 colonnes p1 p2 p3 n'ont rien de particulier, simplement je me posais la question de comment faire pour aller cherche les noms dans table2, et les alias sont impec pour ça.
Effectivement, les réponses de Artemus24 et Escartefigue sont fort pertinentes et bien documentées.

Agréger des données

Requêtes MySQL

Discussions similaires

Partager

Partager