1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| WITH T_SERVICE AS ( SELECT 'SER1' ID, 'Service 1' Nom FROM dual
UNION ALL SELECT 'SER2' ID, 'Service 2' Nom FROM dual
),
T_BUREAU AS ( SELECT 'BUR1' ID, 'SER1' FKIDService, 'Bureau 1' Nom FROM dual
UNION ALL SELECT 'BUR2' ID, 'SER1' FKIDService, 'Bureau 2' Nom FROM dual
),
T_PERS AS ( SELECT 'PERS1' ID, 'BUR1' FKIDBureau, 'Personne 1' Nom FROM dual
UNION ALL SELECT 'PERS2' ID, 'BUR2' FKIDBureau, 'Personne 2' Nom FROM dual
),
T_CAFE AS ( SELECT 'CAFE1' ID, 'PERS1' FKIDPers, '12/02/2008' as Date_c FROM dual
UNION ALL SELECT 'CAFE2' ID, 'PERS1' FKIDPers, '13/02/2008' as Date_c FROM dual
UNION ALL SELECT 'CAFE3' ID, 'PERS1' FKIDPers, '14/02/2008' as Date_c FROM dual
UNION ALL SELECT 'CAFE4' ID, 'PERS2' FKIDPers, '15/02/2008' as Date_c FROM dual
)
SELECT DISTINCT p.nom, b.nom, s.nom, max(c.Date_c) over(partition BY p.id ) AS Date_c
FROM T_CAFE c INNER JOIN T_PERS p ON p.id = c.FKIDPers
INNER JOIN T_BUREAU b ON b.id = p.FKIDBureau
INNER JOIN T_SERVICE s ON s.id = b.FKIDService |
Partager