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
|
with commerce as
(
SELECT 'comm1' as nom_commerce, 35 as id_user from dual UNION ALL
SELECT 'comm2' as nom_commerce, 35 as id_user from dual UNION ALL
SELECT 'comm3' as nom_commerce, 35 as id_user from dual UNION ALL
SELECT 'comm1' as nom_commerce, 36 as id_user from dual UNION ALL
SELECT 'comm2' as nom_commerce, 36 as id_user from dual UNION ALL
SELECT 'comm3' as nom_commerce, 36 as id_user from dual
), evenement as
(
SELECT 'event1' as nom_event, 34 as id_user from dual UNION ALL
SELECT 'event2' as nom_event, 34 as id_user from dual UNION ALL
SELECT 'event3' as nom_event, 34 as id_user from dual UNION ALL
SELECT 'event4' as nom_event, 34 as id_user from dual UNION ALL
SELECT 'event1' as nom_event, 36 as id_user from dual UNION ALL
SELECT 'event2' as nom_event, 36 as id_user from dual UNION ALL
SELECT 'event3' as nom_event, 36 as id_user from dual UNION ALL
SELECT 'event4' as nom_event, 36 as id_user from dual
), utilisateur as
(
SELECT 34 as id_user, 'user34' as nom FROM DUAL UNION ALL
SELECT 35 as id_user, 'user35' as nom FROM DUAL UNION ALL
SELECT 36 as id_user, 'user36' as nom FROM DUAL
)
SELECT donnees.id_user, donnees.QUOI, donnees.data
FROM
(
SELECT tot.id_user, '1-NOMBRE TOTAL' as QUOI, to_char(count(1)) data FROM
( SELECT u.id_user FROM evenement e INNER JOIN utilisateur u ON u.id_user = e.id_user
UNION ALL
SELECT u.id_user FROM commerce c INNER JOIN utilisateur u ON u.id_user = c.id_user
) tot
GROUP BY tot.id_user
UNION ALL
SELECT u.id_user, '2-EVENEMENT' as QUOI, e.nom_event FROM evenement e INNER JOIN utilisateur u ON u.id_user = e.id_user
UNION ALL
SELECT u.id_user, '3-COMMERCE' as QUOI, c.nom_commerce FROM commerce c INNER JOIN utilisateur u ON u.id_user = c.id_user
) donnees
--Where donnees.id_user = 36
ORDER BY donnees.id_user, donnees.QUOI
; |
Partager