Discussion :

[Aurelien_P]

Bonjour à toutes et tous,

Je suis étudiant chercheur en histoire et je travaille sur les procès pour faux témoignage aux XVIIe-XVIIIe siècles. J'ai fait une base de données mysql avec les procès, ce qui me permet e faire des analyses statistiques.

Je rencontre un problème sur lequel je butte depuis quelques jours et que mes recherches sur internet n'ont pas réussi à résoudre pour l'instant...

J'ai une table Individu et une table Sentence (les peines des individus condamnés). Chaque individu peut être condamné à plusieurs peines. J'ai donc une table de jointure Detail_sentence et avec des résultats comme (je rajoute les noms de sentence pour plus de précisions, la table Detail_sentence ne les contient pas):

Individu_id	Sentence_id	Nom de la sentence
1	1	Infamie
1	2	Corporelle
2	1	Infamie
2	3	Mort
2	4	Amende
3	2	Corporelle
3	4	Amende
4	4	Amende

Mon problème est le suivant : je veux faire une requête qui renverrait comme résultat la peine la plus importante pour chaque individu. Dans ce cas (théorique) ce serait donc :
Individu_1 = Corporelle
Individu_2 = Mort
Individu_3 = Corporelle
Individu_4 = Amende

J'ai essayé pas mal de choses, en dupliquant la table détail_sentence ou avec des case when, mais je n'ai pas trouvé la solution ... bien que je me dise que ça ne doit pas être si difficile que cela...

Merci d'avance pour votre aide

[Aurelien_P]

Bon, j'ai trouvé une solution en ajoutant une colonne (Nbr_force) dans la table detail_sentence avec un chiffre pour chaque peine (dans l'ordre ascendant, puis j'ai fait un MAX() group by et une jointure, ce qui pour l'exemple donné ferait :

Code :

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

1
2
3
4
SELECT Detail_sentence.* 
FROM Detail_sentence AS DS 
 INNER JOIN (Select Individu_id, MAX(Nbr_force) AS Max_force  FROM Detail_sentence GROUP BY Individu_id) AS DS2 
  ON DS2.Max_force=DS.Nbr_force AND DS2.Individu_id=DS.Individu_id;

... et ça fonctionne

Au cas où quelqu'un aurait une solution plus simple, je laisse le sujet ouvert quelques jours avant de le fermer.

Bonne soirée !

[Artemus24]

Salut Aurelien_P.

Il y a cette solution :

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
--------------
select individu_id,
       case min(case nom_sentence when 'Corporelle' then 1 when 'Mort' then 2 when 'Infamie' then 3 when 'Amende' then 4 else null end)
                                  when 1 then 'Corporelle' when 2 then 'Mort' when 3 then 'Infamie' when 4 then 'Amende' else '-'  end as nom
from detail_sentence
group by individu_id
--------------
 
+-------------+------------+
| individu_id | nom        |
+-------------+------------+
|           1 | Corporelle |
|           2 | Mort       |
|           3 | Corporelle |
|           4 | Amende     |
+-------------+------------+

@+

[Artemus24]

Salut Aurelien_P.

Et voici une autre solution :

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
--------------
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 `ordre`
--------------
 
--------------
CREATE TABLE `ordre`
( `id`  integer unsigned NOT NULL auto_increment primary key,
  `val` varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `ordre` (`val`) values
  ('jaune'),('vert'),('bleu'),('rouge')
--------------
 
--------------
select * from ordre
--------------
 
+----+-------+
| id | val   |
+----+-------+
|  1 | jaune |
|  2 | vert  |
|  3 | bleu  |
|  4 | rouge |
+----+-------+
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`       integer unsigned NOT NULL auto_increment primary key,
  `tri`      integer unsigned NOT NULL,
  `ordre_id` integer unsigned NOT NULL,
  CONSTRAINT `FK_test_orderid` FOREIGN KEY (`ordre_id`) REFERENCES `ordre` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`tri`, `ordre_id`) values
  (1,2),(1,1),(2,3),(2,2),(3,3),(3,4),(4,1),(4,4),(5,3),(5,4),(6,2),(6,4)
--------------
 
--------------
select * from test
--------------
 
+----+-----+----------+
| id | tri | ordre_id |
+----+-----+----------+
|  1 |   1 |        2 |
|  2 |   1 |        1 |
|  3 |   2 |        3 |
|  4 |   2 |        2 |
|  5 |   3 |        3 |
|  6 |   3 |        4 |
|  7 |   4 |        1 |
|  8 |   4 |        4 |
|  9 |   5 |        3 |
| 10 |   5 |        4 |
| 11 |   6 |        2 |
| 12 |   6 |        4 |
+----+-----+----------+
--------------
select tb1.id, tb1.tri, tb2.val
 
from       test  as tb1
inner join ordre as tb2
on tb2.id = tb1.ordre_id
 
order by tb1.id
--------------
 
+----+-----+-------+
| id | tri | val   |
+----+-----+-------+
|  1 |   1 | vert  |
|  2 |   1 | jaune |
|  3 |   2 | bleu  |
|  4 |   2 | vert  |
|  5 |   3 | bleu  |
|  6 |   3 | rouge |
|  7 |   4 | jaune |
|  8 |   4 | rouge |
|  9 |   5 | bleu  |
| 10 |   5 | rouge |
| 11 |   6 | vert  |
| 12 |   6 | rouge |
+----+-----+-------+
--------------
select tb1.tri, substring_index(group_concat(distinct tb2.val order by tb2.id), ',', 1) as val
 
from       test  as tb1
inner join ordre as tb2
   on tb2.id = tb1.ordre_id
 
group by tb1.tri
order by tb1.tri
--------------
 
+-----+-------+
| tri | val   |
+-----+-------+
|   1 | jaune |
|   2 | vert  |
|   3 | bleu  |
|   4 | jaune |
|   5 | bleu  |
|   6 | vert  |
+-----+-------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

La table "ordre" sert d'une part à externaliser la valeur et d'autre part à la trier selon l'ordre (la colonne "id") que tu désires.
L'astuce est de passer par la fonction "group_concat" afin de concaténer toutes les valeurs dans l'ordre de la table "ordre".
Puis ensuite récupérer la première valeur, en utilisant la fonction "substring_index".

Avec cette astuce, tu peux soit récupérer la première valeur, la dernière valeur et dans l'ordre que tu désires.

@+

[Aurelien_P]

Super ! Je vais regarder ca de plus près en testant sur la base.

Merci beaucoup pour tes deux requêtes et pour ton temps.
Bonne soirée