SQL - Jointure et Select dans Select avec MAX

Version imprimable

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.

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

Code:

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

19/08/2015, 13h37
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.
19/08/2015, 13h57
McM

Citation:

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

Citation:

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).
19/08/2015, 14h04
coeurdange

Citation:

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)

Citation:

Envoyé par McM

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

Code:

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

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

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