Recherche sur plusieurs table avec un count

**beuzz4001** · 24/05/2019, 10h15

Bonjour

Je viens poser ma question sur le forum car je m'arrache les cheveux avec une requete.
j'avoue ne pas trop savoir par quel bout la prendre.

voici ce que je voudrais faire :
J'ai deux tables
table A :

ID	numéro de département	adresse
1	94	rue machin
2	94	rue bidule
3	75	av général
4	75	route pouet

Table B :

ID	ID_tableA	status_machines
1	1	1
2	1	2
3	1	3
4	1	1
5	2	1
6	2	2
7	2	4
8	3	2
9	4	2
9	4	1

les valeurs de status machine :
1 = machine indemne
2 = machine hs
3 = machine a réparré
4 = machine en cours de réparation

je voudrais faire une requête qui donne les résultats suivant :

numéro de département	nbr d'adresse dans le département	nbr_machine_indemne/departement	nbr_machine_hs/departement	nbr_machine_a_reparrer/departement	nbr_machine_en_cours_de_reparation/departement
94	7	3	2	1	1
75	3	1	2	0	0

j'arrive à compter le nombre d'adresses par département comme ceci :

Code :

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

1
2
3
SELECT
(SELECT COUNT(ID_tableA) FROM tableA WHERE numero de departement = 94) AS nbr_adresse_dans_le_94,
(SELECT COUNT(ID_tableA) FROM tableA WHERE numero de departement = 75) AS nbr_adresse_dans_le_75

mais pour avoir l'état des machine par département ?? je trouve pas
en plus ma requête est pas optimisé car je fait des alias par département.

si quelq'un a une idée.
Merci

**tatayo** · 24/05/2019, 10h23

Bonjour,
Tu peux regarder dans cette discussion, il avait un besoin similaire.

Tatayo.

**Michel.Priori** · 24/05/2019, 11h27

Bonjour,

C'est une très bonne idée de savoir ce à quoi on veut arriver. Bonne démarche.

Ce que je note c'est qu'il existe un nombre connu d'avance de "status". C'est une TRES bonne nouvelle.

Je te propose de suivre la réflexion pas à pas.

donc pour arriver à :

Depno	nb_status	nb_status1	nb_status2	nb_status3	nb_status4
94	7	3	2	1	1
75	3	1	2	0	0

Que faudrait-il comme source ?
Sachant que COUNT et GROUP BY vont nous faire les calculs.

En défaisant intellectuellement le travail de l'agrégation, il faudrait partir d'une source comme ça :

dep	s1	s2	s3	s4
94	1
94		1
94			1
94	1
94	1
94		1
75		1
75				1
75				1

Pour "ventiler" les valeurs dans les différentes colonnes en fonction de la valeur de status, l'utilisation du CASE semble toute indiquée.

Vu l'exemple fourni, l'utilisation des agrégations semble connue.
Reste éventuellement le CASE.

Adapte ce bout de code

Code :

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

1
2
3
4
5
6
select depno
	, case status when 1 then 1 else null as s1
	, case status when 2 then 1 else null as s2
	, case status when 3 then 1 else null as s3
	, case status when 4 then 1 else null as s4
From TableA inner join TableB on TableB.idTA = TableA.idTA

**beuzz4001** · 24/05/2019, 15h15

merci pour les propositions.

perso j'ai rien compris au CASE.

j'arrive a faire ce que je veux pour un département mais je n'arrive pas a avoir un résultat globale dans un tableau avec tous les départements.

voici une requete :

Code :

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

1
2
3
4
5
6
SELECT
(SELECT COUNT(id) FROM tableA WHERE num_departement = 94 AND id IN (SELECT id_tableA FROM tableB WHERE satuts_machin = 1)) AS nbr_machine_indemne
(SELECT COUNT(id) FROM tableA WHERE num_departement = 94 AND id IN (SELECT id_tableA FROM tableB WHERE satuts_machin = 2)) AS nbr_machine_hs 
(SELECT COUNT(id) FROM tableA WHERE num_departement = 94 AND id IN (SELECT id_tableA FROM tableB WHERE satuts_machin = 3)) AS nbr_machine_reparé
(SELECT COUNT(id) FROM tableA WHERE num_departement = 94 AND id IN (SELECT id_tableA FROM tableB WHERE satuts_machin = 4)) AS nbr_machine_en_reparration
(SELECT COUNT(id) FROM tableA WHERE num_departement = 94) as nbr_machine_dans_le_94

resultat :
94 | 7 | 3 | 2 | 1 | 1

mais bon vu le nombre de machine et le nombre de département dans la vrais vie les requetes sont un peut longue.

**Michel.Priori** · 24/05/2019, 15h23

Envoyé par beuzz4001

perso j'ai rien compris au CASE.

L'instruction CASE permet de fournir une réponse en fonction de condition(s).

par exemple case status when 3 then 1 else null END as s1 exprime : pour la colonne s1, si "status" vaut 3 alors la valeur sera 1, sinon la valeur sera null.

Est-ce que la transposition par rapport à vos tables, nom de colonne et valeurs a donné le tableau attendu ?

Edit : Oups

j'ai raté le END qui conclue l'instruction CASE - depuis le début.

**beuzz4001** · 24/05/2019, 15h46

ha je me disait bien aussi que la syntaxe n'était pas bonne

**Artemus24** · 25/05/2019, 16h58

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
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
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `adresse`
--------------
 
--------------
create table `adresse`
( `id`           integer  unsigned  not null auto_increment primary key,
  `departement`  smallint unsigned  not null,
  `adresse`      text               not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `adresse` (`departement`,`adresse`) VALUES
  (94, 'rue machin'),
  (94, 'rue bidule'),
  (75, 'av général'),
  (75, 'route pouet')
--------------
 
--------------
select * from `adresse`
--------------
 
+----+-------------+-------------+
| id | departement | adresse     |
+----+-------------+-------------+
|  1 |          94 | rue machin  |
|  2 |          94 | rue bidule  |
|  3 |          75 | av général  |
|  4 |          75 | route pouet |
+----+-------------+-------------+
--------------
DROP TABLE IF EXISTS `machine`
--------------
 
--------------
create table `machine`
( `id`      integer  unsigned  not null auto_increment primary key,
  `statut`  varchar(255)           not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `machine` (`statut`) VALUES
  ('machine indemne'),('machine hs'),('machine à réparrer'),('machine en cours de réparation')
--------------
 
--------------
select * from `machine`
--------------
 
+----+--------------------------------+
| id | statut                         |
+----+--------------------------------+
|  1 | machine indemne                |
|  2 | machine hs                     |
|  3 | machine à réparrer             |
|  4 | machine en cours de réparation |
+----+--------------------------------+
--------------
DROP TABLE IF EXISTS `truc_chose`
--------------
 
--------------
create table `truc_chose`
( `id`            integer  unsigned  not null auto_increment primary key,
  `clef_adresse`  integer  unsigned  not null,
  `clef_machine`  integer  unsigned  not null,
  CONSTRAINT `FK_ADRESSE` FOREIGN KEY (`clef_adresse`) REFERENCES `adresse` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_MACHINE` FOREIGN KEY (`clef_machine`) REFERENCES `machine` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `truc_chose` (`clef_adresse`,`clef_machine`) VALUES
  (1, 1),(1, 2),(1, 3),(1, 1),
  (2, 1),(2, 2),(2, 4),
  (3, 2),
  (4, 1),(4, 2)
--------------
 
--------------
select * from `truc_chose`
--------------
 
+----+--------------+--------------+
| id | clef_adresse | clef_machine |
+----+--------------+--------------+
|  1 |            1 |            1 |
|  2 |            1 |            2 |
|  3 |            1 |            3 |
|  4 |            1 |            1 |
|  5 |            2 |            1 |
|  6 |            2 |            2 |
|  7 |            2 |            4 |
|  8 |            3 |            2 |
|  9 |            4 |            1 |
| 10 |            4 |            2 |
+----+--------------+--------------+
--------------
select      *
      from  `truc_chose`  as t1
 
inner join  `adresse` as t2
        on  t2.id = t1.clef_adresse
 
inner join  `machine` as t3
        on  t3.id = t1.clef_machine
--------------
 
+----+--------------+--------------+----+-------------+-------------+----+--------------------------------+
| id | clef_adresse | clef_machine | id | departement | adresse     | id | statut                         |
+----+--------------+--------------+----+-------------+-------------+----+--------------------------------+
|  1 |            1 |            1 |  1 |          94 | rue machin  |  1 | machine indemne                |
|  2 |            1 |            2 |  1 |          94 | rue machin  |  2 | machine hs                     |
|  3 |            1 |            3 |  1 |          94 | rue machin  |  3 | machine à réparrer             |
|  4 |            1 |            1 |  1 |          94 | rue machin  |  1 | machine indemne                |
|  5 |            2 |            1 |  2 |          94 | rue bidule  |  1 | machine indemne                |
|  6 |            2 |            2 |  2 |          94 | rue bidule  |  2 | machine hs                     |
|  7 |            2 |            4 |  2 |          94 | rue bidule  |  4 | machine en cours de réparation |
|  8 |            3 |            2 |  3 |          75 | av général  |  2 | machine hs                     |
|  9 |            4 |            1 |  4 |          75 | route pouet |  1 | machine indemne                |
| 10 |            4 |            2 |  4 |          75 | route pouet |  2 | machine hs                     |
+----+--------------+--------------+----+-------------+-------------+----+--------------------------------+
--------------
select      t2.departement,
            count(t2.departement) as nbre_adresse,
            sum(case t3.id when 1 then 1 else 0 end) as nbr_machine_indemne,
            sum(case t3.id when 2 then 1 else 0 end) as nbr_machine_hs,
            sum(case t3.id when 3 then 1 else 0 end) as nbr_machine_a_reparre,
            sum(case t3.id when 4 then 1 else 0 end) as nbr_machine_en_cours_de_reparation
 
      from  `truc_chose`  as t1
 
inner join  `adresse` as t2
        on  t2.id = t1.clef_adresse
 
inner join  `machine` as t3
        on  t3.id = t1.clef_machine
 
  group by  t2.departement
--------------
 
+-------------+--------------+---------------------+----------------+-----------------------+------------------------------------+
| departement | nbre_adresse | nbr_machine_indemne | nbr_machine_hs | nbr_machine_a_reparre | nbr_machine_en_cours_de_reparation |
+-------------+--------------+---------------------+----------------+-----------------------+------------------------------------+
|          94 |            7 |                   3 |              2 |                     1 |                                  1 |
|          75 |            3 |                   1 |              2 |                     0 |                                  0 |
+-------------+--------------+---------------------+----------------+-----------------------+------------------------------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

**beuzz4001** · 27/05/2019, 10h07

OK merci pour vos réponses.

alors j'ai compris le principe pour la condition Case, mais je ne suis pas sur que cela me soit utile.
quand au jointure de table , je ne vois pas trop l’intérêt car tous ce qui est dans la TABLEA est aussi dans le TABLEB.

du coup j'ai combiné une requette QSL avec un peut de PHP ce qui est beaucoup plus simple et rapide :
rappel des tables
table A :

ID_tableA	dep	adresse
1	94	rue machin
2	94	rue bidule
3	75	av général
4	75	route pouet

Table B :

ID_tableB	ID_tableA	status_machines
1	1	1
2	1	2
3	1	3
4	1	1
5	2	1
6	2	2

Code php :

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
<?php 
 
$machine  = array("indemne"=>1, "hs"=>2, "reperrer"=>3, "reparation_encours"=>4);
 
foreach( $machine as $status => $value){
$sql = '
SELECT dep, COUNT(ID_tableA) AS relsult
FROM tableA IN (SELECT ID_tableA FROM tableB WHERE status_machines = '.$value.')
GROUP BY dep
ORDER BY dep
';
 
//execution de la requete sql:				
$req = mysqli_query($db, $sql) or die ('Erreur SQL : <br /></br>'.mysqli_error($db));
while ( $data = mysqli_fetch_assoc($req))
$gravite[$data['dep']][$status] = $data['result'];
}
 
print_r($gravite)
?>

ce qui nous donne dans $gravite

Code :

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

1
2
3
4
5
6
7
8
Array (
[1] => Array ( [indemne] => 237 [hs] => 42 [reperrer] => 262 [reparation_encours] => 172 ) 
[2] => Array ( [indemne] => 157 [hs] => 37 [reperrer] => 156 [reparation_encours] => 82 )
[3] => Array ( [indemne] => 146 [hs] => 23 [reperrer] => 136 [reparation_encours] => 123 )
[4] => Array ( [indemne] => 104 [hs] => 25 [reperrer] => 124 [reparation_encours] => 82 )
[5] => Array ( [indemne] => 140 [hs] => 11 [reperrer] => 97 [reparation_encours] => 111 )
[6] => Array ( [indemne] => 1009 [hs] => 53 [reperrer] => 693 [reparation_encours] => 685 )
etc....

$gravite est facilement exploitable avec du php ou JS.

Merci pour vos proposition.

Je passe le sujet en résolu

**Michel.Priori** · 30/05/2019, 12h01

Bonjour beuzz4001,

Pourquoi diable faire une boucle ?

Tu as fais un premier chargement pour avoir la liste des états.
Puis pour chaque valeur, tu (re)lances une requête pour aller chercher la suite et ce autant de fois qu'il y a d'état.
Ici on a 4 états => 4 fois plus long qu'une seule requête.
Sans parler du fait que coté PHP le travail de jointure sur le n° département est à refaire.
Bref, ce n'est pas optimal

Il vaut mieux travailler les requêtes pour elles mêmes, puis les placer dans le code.
MySQLWorkbench est un bon atelier

En reprenant le pas à pas de mon précédent post, l'étape suivante à laquelle je pensais, est de capitaliser le résultat du tableau avec une fonction d'agréation et un GROUP BY

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
select D.depno
		, count(D.s1) as Nb_S1
		, count(D.s2) as Nb_S2
		, count(D.s3) as Nb_S3		
		, count(D.s4) as Nb_S4		
from (select depno
			, case status when 1 then 1 else null end as s1
			, case status when 2 then 1 else null end as s2
			, case status when 3 then 1 else null end as s3
			, case status when 4 then 1 else null end as s4
		From TableA inner join TableB on TableB.idTA = TableA.idTA
	  ) D
Group by depno

Au choix de la fonction près, Artemus24 et moi, te disons la même chose.
Chacun à sa manière.

**Artemus24** · 30/05/2019, 14h43

Salut à tous.

@ beuzz4001 : Je vous ai donné la solution à votre problème.
Qu'est-ce que vous ne comprenez pas ?
Qu'est-ce qui ne vous convient pas ?

Comme dit Michel.Priori, le fait d'utiliser une boucle en php n'est pas optimal.

@+

Recherche sur plusieurs table avec un count

Requêtes MySQL

Discussions similaires

Partager

Partager