J'ai deux tables T1 (Parent_Table) et T2 (Child_Table)
T1:
ID | File | ASSURANCE | VAL | 1 | L1 | ASSR1 | 0 | - Matched row from T1 for id=1 and lang=l1 1 | F1 | ASSR1 | 0 | - Matched row from T2 for id=1 and lang=l1 1 | F2 | ASSR1 | 4 | - Matched row from T2 for id=1 and lang=l1 1 | L2 | ASSR2 | 1 | - Matched row from T1 for id=1 and lang=l2 1 | F3 | ASSR2 | 8 | - Matched row from T2 for id=1 and lang=l2 1 | F4 | ASSR2 | 3 | - Matched row from T2 for id=1 and lang=l2 1 | L3 | ASSR3 | 5 | - Matched row from T1 for id=1 and lang=l3 1 | F5 | ASSR3 | 7 | - Matched row from T2 for id=1 and lang=l3 1 | F6 | ASSR3 | 0 | - Matched row from T2 for id=1 and lang=l3
T2:
ID | LANG | File | ASSURANCE | VAL | 1 | L1 | F1 | ASSR1 | 0 | 1 | L1 | F2 | ASSR1 | 4 | 1 | L2 | F3 | ASSR2 | 8 | 1 | L2 | F4 | ASSR2 | 3 | 1 | L3 | F5 | ASSR3 | 7 | 1 | L3 | F6 | ASSR3 | 0 |
Sortie attendue:
ID | LANG | ASSURANCE | VAL | 1 | L1 | ASSR1 | 0 | 1 | L2 | ASSR2 | 1 | 1 | L3 | ASSR3 | 5 |
Condition:
Je veux traverser T1 pour id = 1 et pour chaque langue, je veux d'abord sortir cette ligne (à partir de T1), puis traverse en T2 pour l'id et la langue actuelle et la liste des enregistrements correspondants dans T2 (pour l'id et la langue actuelle) puis continuez avec la langue suivante dans T1 jusqu'à ce que toutes les langues soient traitées dans T1.
Est-il possible d'y parvenir en utilisant une seule requête Oracle SQL?
Merci. p >
3 Réponses :
Quelque chose comme ça?
SQL> with t1 (id, lang, assurance, val) as
2 (select 1, 'L1', 'assr1', 0 from dual union all
3 select 1, 'L2', 'assr2', 1 from dual union all
4 select 1, 'L3', 'assr3', 5 from dual
5 ),
6 t2 (id, lang, cfile, assurance, val) as
7 (select 1, 'L1', 'F1', 'assr1', 0 from dual union all
8 select 1, 'L1', 'F2', 'assr1', 4 from dual union all
9 select 1, 'L2', 'F3', 'assr2', 8 from dual union all
10 select 1, 'L2', 'F4', 'assr2', 3 from dual union all
11 select 1, 'L3', 'F5', 'assr3', 7 from dual union all
12 select 1, 'L3', 'F6', 'assr3', 0 from dual
13 ),
14 inter as
15 (select t1.id, t1.lang, t1.lang cfile, t1.assurance, t1.val from t1
16 union all
17 select t2.id, t2.lang, t2.cfile, t2.assurance, t2.val from t2
18 )
19 select id, cfile, assurance, val
20 from inter
21 order by id, lang, cfile desc;
ID CF ASSUR VAL
---------- -- ----- ----------
1 L1 assr1 0
1 F2 assr1 4
1 F1 assr1 0
1 L2 assr2 1
1 F4 assr2 3
1 F3 assr2 8
1 L3 assr3 5
1 F6 assr3 0
1 F5 assr3 7
9 rows selected.
SQL>
Essayez ceci: sortie:
Collez les tableaux en utilisant union all , en ajoutant des informations sur la source des lignes. Pour le premier tableau, ajoutez également la colonne lang comme file_ . Enfin, triez cette union.
select id, file_, assurance, val
from (
select id, lang, lang file_, assurance, val, 't1' source from t1 where id = 1
union all
select id, lang, file_, assurance, val, 't2' source from t2 where id = 1)
order by lang, source, file_