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 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75
|
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as mni
SQL>
SQL> WITH departements AS(
2 SELECT 'Alsace' lib_dep, 1 id_reg
3 FROM DUAL
4 UNION ALL
5 SELECT 'Paris' lib_dep, 2 id_reg
6 FROM DUAL
7 ), region AS (
8 SELECT 1 id_reg, 'Bas-Rhin' libelle_reg
9 FROM DUAL
10 UNION ALL
11 SELECT 1 id_reg, 'Haut-Rhin' libelle_reg
12 FROM DUAL
13 UNION ALL
14 SELECT 1 id_reg, 'Aquitaine' libelle_reg
15 FROM DUAL
16 UNION ALL
17 SELECT 1 id_reg, 'Dordogne' libelle_reg
18 FROM DUAL
19 UNION ALL
20 SELECT 1 id_reg, 'Gironde' libelle_reg
21 FROM DUAL
22 UNION ALL
23 SELECT 1 id_reg, 'Landes' libelle_reg
24 FROM DUAL
25 UNION ALL
26 SELECT 1 id_reg, 'Lot-et-Garonne' libelle_reg
27 FROM DUAL
28 UNION ALL
29 SELECT 1 id_reg, 'Pyrénées-Atlantiques' libelle_reg
30 FROM DUAL
31 UNION ALL
32 SELECT 2 id_reg, 'Paris1' libelle_reg
33 FROM DUAL
34 UNION ALL
35 SELECT 2 id_reg, 'Paris2' libelle_reg
36 FROM DUAL
37 UNION ALL
38 SELECT 2 id_reg, 'Paris3' libelle_reg
39 FROM DUAL
40 ), Agg_Data As (
41 Select id_reg, lib_dep, 0 as tri
42 from departements
43 Union All
44 Select id_reg, libelle_reg, 1 as tri
45 from region
46 )
47 Select lib_dep
48 From agg_data
49 Order By id_reg, tri, lib_dep
50 /
LIB_DEP
--------------------
Alsace
Aquitaine
Bas-Rhin
Dordogne
Gironde
Haut-Rhin
Landes
Lot-et-Garonne
Pyrénées-Atlantiques
Paris
Paris1
Paris2
Paris3
13 rows selected
SQL> |
Partager