SQL - Jointure et Select dans Select avec MAX

**coeurdange** · 19/08/2015, 11h31

Bonjour,

Pourriez-vous m'aider concernant l'élaboration d'une vue en SQL sur une base de données contenant des tables d'historisation?

Le context est le suivant: quand une mise à jour (à une date t) est réalisée, une nouvelle entrée (nouvel ID HIST) est insérée dans la table d'historisation correspondante.

Voici la structure des tables de ma base de données:

Table contrat HIST (informations générales)

ID HIST	Date Creation	ID Contrat	Titre Contrat	Description	Budget
1	01/01/2015	1	Contrat 1	Contrat Informatique	20 000
2	15/01/2015	1	Contrat 1	Contrat Informatique	50 000
3	02/02/2015	2	Contrat 2	Contrat Santé	10 000
4	01/03/2015	2	Contrat 2	Contrat Consommateur	30 000
5	01/07/2015	1	Contrat 1	Contrat Informatique	50 000

Table MODULE HIST

ID HIST	Date Creation	ID Module	Titre Module	ID Contrat
13	04/01/2015	1	Module 1	1
18	19/01/2015	1	Module 1	1
18	19/01/2015	2	Module 2	1
30	10/02/2015	1	Module 1	2
30	10/02/2015	2	Module 2	2
69	19/02/2015	1	Module 1	2
71	07/03/2015	2	Module 2	2
71	07/03/2015	3	Module 3	2
82	25/07/2015	1	Module 1	1
82	25/07/2015	3	Module 3	1

Les identifants (ID HIST) des 2 tables sont différents et non liés seul les identifiants du contrat (ID Contrat) sont liés.

J'aimerais que la vue récupère toutes les mises à jour (à partir de ID_HIST et Date Creation) des 2 tables triées par date. Si la mise à jour concerne la table des modules alors j'aimerais récuperer les dernières valeurs de la table contrat (les valeurs de la dernière ligne précédent la date de mise à jour pour le contrat concernant), comme ceci:

ID HIST	Date Creation	ID Contrat	Titre Contrat	Description	Budget	Commentaire
1	01/01/2015	1	Contrat 1	Contrat Informatique	20 000	Phase 1 - Création du contrat 1
13	04/01/2015	1	Contrat 1	Contrat Informatique	20 000	Phase 2 - Ajout du module 1 au contrat 1
2	15/01/2015	1	Contrat 1	Contrat Informatique	50 000	Phase 3 - Modification des données du contrat 1
18	19/01/2015	1	Contrat 1	Contrat Informatique	50 000	Phase 4 - Ajout des modules 1 et 2 au contrat 1
3	02/02/2015	2	Contrat 2	Contrat Santé	10 000	Phase 5 - Création du contrat 2
30	10/02/2015	2	Contrat 2	Contrat Santé	10 000	Phase 6 - Ajout des modules 1 et 2 au contrat 2
69	19/02/2015	2	Contrat 2	Contrat Santé	10 000	Phase 7 - Suppression du module 1 pour le contrat 2
4	01/03/2015	2	Contrat 2	Contrat Consommateur	30 000	Phase 8 - Modification des données du contrat 2
71	07/03/2015	2	Contrat 2	Contrat Consommateur	30 000	Phase 9 - Attribution des modules 2 et 3 au contrat 2
5	01/07/2015	1	Contrat 1	Contrat Informatique	50 000	Phase 10 - Modification des données du contrat 1
82	25/07/2015	1	Contrat 1	Contrat Informatique	50 000	Phase 11 - Attribution des modules 1 et 3 au contrat 1

La colonne commentaire est juste à titre d'information pour comprendre le jeu d'essai.

Je ne parviens pas à faire la partie sur la récupération des mises à jour des modules munis des dernières info du contrat concerné (dernière ligne des contrats dont la date est juste avant la date de mise à jour du module).

Merci d'avance pour votre aide.

**McM** · 19/08/2015, 13h28

J'ai mis un distinct sur les modules car visiblement c'est ce que tu veux.

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
WITH contrat AS (SELECT 1 id_hist, TO_DATE('01/01/2015', 'DD/MM/RRRR') datecrea, 1 idcontrat, 'Contrat 1' titre, 'Contrat Informatique' description, 20000 budget FROM dual
UNION ALL SELECT 2 id_hist, TO_DATE('15/01/2015', 'DD/MM/RRRR') datecrea, 1 idcontrat, 'Contrat 1' titre, 'Contrat Informatique' description, 50000 budget FROM dual
UNION ALL SELECT 3 id_hist, TO_DATE('02/02/2015', 'DD/MM/RRRR') datecrea, 2 idcontrat, 'Contrat 2' titre, 'Contrat Santé' description, 10000 budget FROM dual
UNION ALL SELECT 4 id_hist, TO_DATE('01/03/2015', 'DD/MM/RRRR') datecrea, 2 idcontrat, 'Contrat 2' titre, 'Contrat Consommateur' description, 30000 budget FROM dual
UNION ALL SELECT 5 id_hist, TO_DATE('01/07/2015', 'DD/MM/RRRR') datecrea, 1 idcontrat, 'Contrat 1' titre, 'Contrat Informatique' description, 50000 budget FROM dual
), 
module AS (SELECT 13 id_hist, TO_DATE('04/01/2015 ', 'DD/MM/RRRR') datecrea, 1 idmodule, 'Module 1 ' titre, 1 idcontrat FROM dual
UNION ALL SELECT 18 id_hist, TO_DATE('19/01/2015 ', 'DD/MM/RRRR') datecrea, 1 idmodule, 'Module 1 ' titre, 1 idcontrat FROM dual
UNION ALL SELECT 18 id_hist, TO_DATE('19/01/2015 ', 'DD/MM/RRRR') datecrea, 2 idmodule, 'Module 2 ' titre, 1 idcontrat FROM dual
UNION ALL SELECT 30 id_hist, TO_DATE('10/02/2015 ', 'DD/MM/RRRR') datecrea, 1 idmodule, 'Module 1 ' titre, 2 idcontrat FROM dual
UNION ALL SELECT 30 id_hist, TO_DATE('10/02/2015 ', 'DD/MM/RRRR') datecrea, 2 idmodule, 'Module 2 ' titre, 2 idcontrat FROM dual
UNION ALL SELECT 69 id_hist, TO_DATE('19/02/2015 ', 'DD/MM/RRRR') datecrea, 1 idmodule, 'Module 1 ' titre, 2 idcontrat FROM dual
UNION ALL SELECT 71 id_hist, TO_DATE('07/03/2015 ', 'DD/MM/RRRR') datecrea, 2 idmodule, 'Module 2 ' titre, 2 idcontrat FROM dual
UNION ALL SELECT 71 id_hist, TO_DATE('07/03/2015 ', 'DD/MM/RRRR') datecrea, 3 idmodule, 'Module 3 ' titre, 2 idcontrat FROM dual
UNION ALL SELECT 82 id_hist, TO_DATE('25/07/2015 ', 'DD/MM/RRRR') datecrea, 1 idmodule, 'Module 1 ' titre, 1 idcontrat FROM dual
UNION ALL SELECT 82 id_hist, TO_DATE('25/07/2015 ', 'DD/MM/RRRR') datecrea, 3 idmodule, 'Module 3 ' titre, 1 idcontrat FROM dual
)
SELECT id_hist, datecrea, idcontrat, titre, description, budget FROM contrat
UNION ALL
SELECT DISTINCT m.id_hist, m.datecrea, m.idcontrat, c.titre, c.description, c.budget  FROM module m, contrat c
WHERE c.idcontrat = m.idcontrat
AND c.id_hist = (SELECT MAX(id_hist) FROM contrat b WHERE b.idcontrat = m.idcontrat AND b.datecrea < m.datecrea)
ORDER BY 2

**coeurdange** · 19/08/2015, 14h37

Envoyé par McM

J'ai mis un distinct sur les modules car visiblement c'est ce que tu veux.

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
WITH contrat AS (SELECT 1 id_hist, TO_DATE('01/01/2015', 'DD/MM/RRRR') datecrea, 1 idcontrat, 'Contrat 1' titre, 'Contrat Informatique' description, 20000 budget FROM dual
UNION ALL SELECT 2 id_hist, TO_DATE('15/01/2015', 'DD/MM/RRRR') datecrea, 1 idcontrat, 'Contrat 1' titre, 'Contrat Informatique' description, 50000 budget FROM dual
UNION ALL SELECT 3 id_hist, TO_DATE('02/02/2015', 'DD/MM/RRRR') datecrea, 2 idcontrat, 'Contrat 2' titre, 'Contrat Santé' description, 10000 budget FROM dual
UNION ALL SELECT 4 id_hist, TO_DATE('01/03/2015', 'DD/MM/RRRR') datecrea, 2 idcontrat, 'Contrat 2' titre, 'Contrat Consommateur' description, 30000 budget FROM dual
UNION ALL SELECT 5 id_hist, TO_DATE('01/07/2015', 'DD/MM/RRRR') datecrea, 1 idcontrat, 'Contrat 1' titre, 'Contrat Informatique' description, 50000 budget FROM dual
), 
module AS (SELECT 13 id_hist, TO_DATE('04/01/2015 ', 'DD/MM/RRRR') datecrea, 1 idmodule, 'Module 1 ' titre, 1 idcontrat FROM dual
UNION ALL SELECT 18 id_hist, TO_DATE('19/01/2015 ', 'DD/MM/RRRR') datecrea, 1 idmodule, 'Module 1 ' titre, 1 idcontrat FROM dual
UNION ALL SELECT 18 id_hist, TO_DATE('19/01/2015 ', 'DD/MM/RRRR') datecrea, 2 idmodule, 'Module 2 ' titre, 1 idcontrat FROM dual
UNION ALL SELECT 30 id_hist, TO_DATE('10/02/2015 ', 'DD/MM/RRRR') datecrea, 1 idmodule, 'Module 1 ' titre, 2 idcontrat FROM dual
UNION ALL SELECT 30 id_hist, TO_DATE('10/02/2015 ', 'DD/MM/RRRR') datecrea, 2 idmodule, 'Module 2 ' titre, 2 idcontrat FROM dual
UNION ALL SELECT 69 id_hist, TO_DATE('19/02/2015 ', 'DD/MM/RRRR') datecrea, 1 idmodule, 'Module 1 ' titre, 2 idcontrat FROM dual
UNION ALL SELECT 71 id_hist, TO_DATE('07/03/2015 ', 'DD/MM/RRRR') datecrea, 2 idmodule, 'Module 2 ' titre, 2 idcontrat FROM dual
UNION ALL SELECT 71 id_hist, TO_DATE('07/03/2015 ', 'DD/MM/RRRR') datecrea, 3 idmodule, 'Module 3 ' titre, 2 idcontrat FROM dual
UNION ALL SELECT 82 id_hist, TO_DATE('25/07/2015 ', 'DD/MM/RRRR') datecrea, 1 idmodule, 'Module 1 ' titre, 1 idcontrat FROM dual
UNION ALL SELECT 82 id_hist, TO_DATE('25/07/2015 ', 'DD/MM/RRRR') datecrea, 3 idmodule, 'Module 3 ' titre, 1 idcontrat FROM dual
)
SELECT id_hist, datecrea, idcontrat, titre, description, budget FROM contrat
UNION ALL
SELECT DISTINCT m.id_hist, m.datecrea, m.idcontrat, c.titre, c.description, c.budget  FROM module m, contrat c
WHERE c.idcontrat = m.idcontrat
AND c.id_hist = (SELECT MAX(id_hist) FROM contrat b WHERE b.idcontrat = m.idcontrat AND b.datecrea < m.datecrea)
ORDER BY 2

Merci beaucoup pour votre réponse, j'avais effectué un peu la même requête mais m'étais trompé de table dans les jointures. J'ai réécrit la query et maintenant c'est parfait.

Merci à vous

**DanielNobre** · 19/08/2015, 13h37

Salut coeurdange,

Je ne parviens pas à comprendre. Si la seule liaison entre les deux tables est le "ID Contrat" donc , pour le contrat 1 vous devriez avoir 15(3 x 5) lignes.
Aussi vous mélangez dans la vue les "ID HIST" de les deux tables. Le "Budget" n'existe pas dans la table "MODULE HIST" mais il est là dans la vue.

**McM** · 19/08/2015, 13h57

Envoyé par DanielNobre

Aussi vous mélangez dans la vue les "ID HIST" de les deux tables. Le "Budget" n'existe pas dans la table "MODULE HIST" mais il est là dans la vue.

Il a indiqué qu'il voulait les données de contrat

modules munis des dernières info du contrat concerné (dernière ligne des contrats dont la date est juste avant la date de mise à jour du module).

**coeurdange** · 19/08/2015, 14h04

Envoyé par DanielNobre

Salut coeurdange,

Je ne parviens pas à comprendre. Si la seule liaison entre les deux tables est le "ID Contrat" donc , pour le contrat 1 vous devriez avoir 15(3 x 5) lignes.
Aussi vous mélangez dans la vue les "ID HIST" de les deux tables. Le "Budget" n'existe pas dans la table "MODULE HIST" mais il est là dans la vue.

En fait je souhaite récupérer tous les ID HIST munis des valeurs des contrats. Pour la table contrats ce n'est pas un problème, mais pour celle des modules je veux récupérer les dernières valeurs (mises à jour) des contrats en se basant par rapport à la date des modules: dernière ligne correspondant à MAX(HIST_ID) de la table contrat où DATE_CONTRAT<DATE_MODULE (le lien est fait grâce à contrat ID entre les 2 tables)

SQL - Jointure et Select dans Select avec MAX

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager