ORDER BY dans un GROUP BY

**iliak** · 02/04/2012, 14h25

Bonjour,

Dans le cadre d'une logiciel de "gestion hôtelière", j'ai une table qui historise les demandes de logements :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE `t_suivilocatif` (
	`Id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`Passager` INT(10) UNSIGNED NOT NULL COMMENT 'Id du passger',
	`RangVoeu` TINYINT(4) UNSIGNED NULL DEFAULT NULL COMMENT 'Numéro du voeu',
	`Statut` INT(10) UNSIGNED NOT NULL COMMENT 'Id du statut',
	`Residence` INT(10) UNSIGNED NOT NULL COMMENT 'Id de la résidence concernée',
	`Date` INT(10) UNSIGNED NOT NULL COMMENT 'Id de la date de mise à jour de l\'information du statut',
	[....]
	UNIQUE INDEX `UNIQUE_t_suivilocatif` (`Passager`, `RangVoeu`, `Statut` [....]),
)

Un Passager (personne qui demande à être hébergée), fait des demandes de logement (Rangvoeu) dans une Résidence particulière à une Date donnée. Nous lui donnons un Statut (réservation, occupation, départ, abandon...). La colonne "Date" est une clé étrangère sur un table temps :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
CREATE TABLE `t_temps` (
	`Id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`Date` DATE NOT NULL COMMENT 'Date au format "aaaa-mm-jj"',
	`Annee` SMALLINT(4) UNSIGNED NOT NULL COMMENT 'Année (civile)',
	`MoisNumero` TINYINT(3) UNSIGNED NOT NULL COMMENT 'Numéro du mois',
	`MoisNom` VARCHAR(10) NOT NULL COMMENT 'Nom complet du mois' COLLATE 'latin1_swedish_ci',
	[....]
)
COMMENT='Table de temps'

Il y a une clé unique sur le passager, le rangvoeu et le statut. L'idée est de suivre l'évolution du statut de la demande. Pour cela, j'ai besoin de créer une vue qui affiche le tout dernier statut d'une demande.

En cherchant un peu sur le net j'ai trouvé cette page ("Aggregates" => "Within-group aggregates") qui répond en partie à ma problématique. Le hic est que MySQL n'autorise pas les vues avec des sous requêtes (erreur 1349)

Voici par exemple les enregistrements pour le passager 80. On peut y voir qu'il a fait 3 vœux (ne pas tenir compte du vœu 0) et que pour chaque vœu il y a 2 entrées :

Code :

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

1
2
3
4
5
6
7
8
9
 
| *id*   | *Passager* | *RangVoeu* | *libelle*      | *Residence* | *date*     |
| 133    | 80         | 0          | Occupation     | 21          | 2011-09-08 |
| 135    | 80         | 1          | Réservation    | 21          | 2011-04-12 |
| 134    | 80         | 1          | Attente        | 21          | 2011-04-04 |
| 137    | 80         | 2          | Déjà Réservé   | 7           | 2011-04-05 |
| 136    | 80         | 2          | Attente        | 7           | 2011-02-27 |
| 139    | 80         | 3          | Déjà Réservé   | 26          | 2011-04-05 |
| 138    | 80         | 3          | Attente        | 26          | 2011-02-27 |

Je souhaiterai garder uniquement le dernier statut (donc celui avec la date la plus récenté) pour chaque voeu :

Code :

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

1
2
3
4
5
6
 
| *id*   | *Passager* | *RangVoeu* | *libelle*      | *Residence* | *date*     |
| 133    | 80         | 0          | Occupation     | 21          | 2011-09-08 |
| 135    | 80         | 1          | Réservation    | 21          | 2011-04-12 |
| 137    | 80         | 2          | Déjà Réservé   | 7           | 2011-04-05 |
| 139    | 80         | 3          | Déjà Réservé   | 26          | 2011-04-05 |

**CinePhil** · 02/04/2012, 14h40

Il faut que tu cherches la date maxi de chaque voeu du passager et que tu joignes cette requête à la table pour répcupérer les autres infos.

**iliak** · 03/04/2012, 11h26

Voici la requête qui retourne tout l'historique d'un passager :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
select suivi.id, suivi.Passager, suivi.RangVoeu, statut.libelle, suivi.Residence, date.date
from t_suivilocatif as suivi
 
join t_temps as date               on date.id = suivi.date
join t_statut as statut            on statut.id = suivi.Statut
 
where suivi.passager = 100380 and suivi.anneegestion = 2011
 
 
order by suivi.Passager, suivi.RangVoeu, date.date desc, statut.Ordre

Le résultat :

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
 
id         Passager  RangVoeu    libelle            Residence    date
36636       100380      0        Occupation         23           2011-09-06
36644       100380      1        Déjà admis         29           2012-02-16
36640       100380      1        Affectation        29           2012-02-16
36642       100380      1        Réservation        29           2012-02-16
36648       100380      1        Déjà Réservé       29           2011-06-14
36646       100380      1        Déjà Affecté       29           2011-06-14
36638       100380      1        Attente            29           2011-06-14
36652       100380      2        Déjà admis         25           2012-02-16
36654       100380      2        Déjà Affecté       25           2011-06-14
36656       100380      2        Déjà Réservé       25           2011-06-14
36650       100380      2        Attente            25           2011-06-14
36660       100380      3        Déjà admis         18           2012-02-16
36662       100380      3        Déjà Affecté       18           2011-06-14
36664       100380      3        Déjà Réservé       18           2011-06-14
36658       100380      3        Attente            18           2011-06-14
36672       100380      4        Abandon            23           2012-02-16
36670       100380      4        Réservation        23           2011-07-05
36668       100380      4        Affectation        23           2011-07-04
36666       100380      4        Attente            7            2011-06-14

La requête qui retourne ce que je souhaite (avoir le dernier statut d'une demande de logement) :

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
 
select suivi.rangvoeu, date.date, statut.Libelle
from 
(
	select s.*
	from t_suivilocatif as s
 
	join t_temps as t        on t.Id = s.Date
	join t_statut as stat    on stat.id = s.Statut
 
	where s.AnneeGestion = 2011 and s.Passager = 100380
 
	order by t.date desc, stat.Ordre
) as suivi
 
join t_statut as statut           on statut.id = suivi.statut
join t_temps as date              on date.id = suivi.date
 
group by suivi.passager, suivi.rangvoeu

Voici le résultat que je souhaite :

Code :

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

1
2
3
4
5
6
7
 
 id        passager  rangvoeu    Libelle        residence        date 
 36636     100380        0       Occupation       23        2011-09-06 
 36644     100380        1       Déjà admis       29        2012-02-16 
 36652     100380        2       Déjà admis       25        2012-02-16 
 36660     100380        3       Déjà admis       18        2012-02-16 
 36672     100380        4       Abandon          23        2012-02-16

Lorsque j'essaie de créer la vue, j'ai l'erreur : SQL Error (1349): View's SELECT contains a subquery in the FROM clause. Je pense qu'il faut modifier la requête pour utiliser une jointure externe, mais sachant que dans ma sous requête j'utilise déjà des jointures, je ne vois pas comment faire des jointures dans des jointures...

**NicoD.** · 03/04/2012, 13h55

Est-ce que cette requête se rapproche du résultat que tu attends ?

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
 
SELECT
	Max(suivi_id) 
	suivi.Passager, 
	suivi.RangVoeu, 
	statut.libelle, 
	suivi.Residence, 
	date.date
FROM 
	t_suivilocatif AS suivi
	INNER JOIN t_temps AS date
		ON date.id = suivi.date
	INNER JOIN t_statut AS statut
		ON statut.id = suivi.Statut
WHERE
	suivi.passager = 100380
	AND suivi.anneegestion = 2011
GROUP BY
	suivi.Passager, 
	suivi.RangVoeu, 
	statut.libelle, 
	suivi.Residence, 
	date.date
ORDER BY 
	suivi.Passager, 
	suivi.RangVoeu, 
	date.date DESC, 
	statut.Ordre
;

**iliak** · 03/04/2012, 14h12

Le problème est que le ORDER BY est effectué après le GROUP BY, donc je récupère le 1° enregistrement rencontré et non pas le dernier en date. Je ne veux pas utiliser la clé primaire (suivi.id) comme un compteur ordinal car je peux très bien insérer des données sans ordre chronologique prédéfini.

Pour les âmes charitables qui souhaiteraient m'aider, voici de quoi créer la table et ses données :

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
 
CREATE TABLE `test` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`Passager` INT(10) NOT NULL,
	`RangVoeu` INT(10) NOT NULL,
	`statut` VARCHAR(50) NOT NULL,
	`Residence` VARCHAR(50) NOT NULL,
	`Date` DATE NOT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
 
 
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36636, 100380, 0, 'Occupation', 23, '2011-09-06');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36644, 100380, 1, 'Déjà admis', 29, '2012-02-16');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36640, 100380, 1, 'Affectation', 29, '2012-02-16');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36642, 100380, 1, 'Réservation', 29, '2012-02-16');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36648, 100380, 1, 'Déjà Réservé', 29, '2011-06-14');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36646, 100380, 1, 'Déjà Affecté', 29, '2011-06-14');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36638, 100380, 1, 'Attente', 29, '2011-06-14');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36652, 100380, 2, 'Déjà admis', 25, '2012-02-16');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36654, 100380, 2, 'Déjà Affecté', 25, '2011-06-14');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36656, 100380, 2, 'Déjà Réservé', 25, '2011-06-14');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36650, 100380, 2, 'Attente', 25, '2011-06-14');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36660, 100380, 3, 'Déjà admis', 18, '2012-02-16');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36662, 100380, 3, 'Déjà Affecté', 18, '2011-06-14');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36664, 100380, 3, 'Déjà Réservé', 18, '2011-06-14');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36658, 100380, 3, 'Attente', 18, '2011-06-14');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36672, 100380, 4, 'Abandon', 23, '2012-02-16');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36670, 100380, 4, 'Réservation', 23, '2011-07-05');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36668, 100380, 4, 'Affectation', 23, '2011-07-04');
INSERT INTO `test` (`id`, `Passager`, `RangVoeu`, `statut`, `Residence`, `date`) VALUES (36666, 100380, 4, 'Attente', 7, '2011-06-14');

Merci d'avance

**CinePhil** · 03/04/2012, 15h16

Apparemment, c'est une lacune de MySQL (une de plus !

) !

En cherchant le message d'erreur sur

, j'ai trouvé une réponse en anglais qui préconise de faire une vue avec la sous-requête pour éviter d'avoir une sous-requête dans la vue principale. Ça pourrait donner un truc de ce genre :

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
CREATE VIEW v_derniers_statuts AS
SELECT Passager, RangVoeu, MAX(`Date`) AS derniere_date
FROM t_suivilocatif
GROUP BY Passager, RangVoeu;
 
CREATE VIEW v_derniers_statuts_avec_details AS
SELECT sv.Id, sv.Passager, sv.RangVoeu, 
	st.libelle AS statut, 
	r.nom AS residence
	t.`date` AS 'Date'
FROM t_suivilocatif sv
INNER JOIN t_statut st ON st.id = sv.Statut
INNER JOIN t_temps AS t ON t.id = sv.`date`
INNER JOIN t_residence r ON r.id = sv.Residence
INNER JOIN v_derniers_statuts v
	ON v.Passager = sv.Passager
	AND v.RangVoeu = sv.RangVoeu
	AND v.derniere_date = sv.`date`;

**iliak** · 04/04/2012, 10h29

Merci CinePhil pour ta requête, qui hélas n'est pas "bonne" mais j'ai suivi ton idée, à savoir utiliser un vue qui trie (avec les bon critères de la logique métier) et dans une autre vue je fais le 'group by' qui va bien.

Merci !

ORDER BY dans un GROUP BY

Requêtes MySQL

Discussions similaires

Partager

Partager