Resultat d'une requete imbriqué

**w3blogfr** · 20/01/2009, 14h21

Bonjour,
je connais pas le nom exact de ce que je veux faire alors je vais essayer de l'expliquer.
J'ai une table parents et une table enfants.

Je sais réaliser la requête me permettant de retourner le nombre d'enfants rattaché à un parent en utilisant un count, un group by et un left outer join.
Par exemple:
parent1 3
parent2 1
etc

Je que je souhaite, c'est afficher la liste des enfants au lieu du nombre.
Par exemple:

parent1 enfant1,enfant2,enfant3
parent2 enfant4
etc

Comment faire?
je ne sais pas comment ca s'appelle et je ne trouve pas grand chose sur internet.
D'avance merci.

**Waldar** · 20/01/2009, 14h33

Celà s'appelle du "string aggregation".
Vous trouverez votre bonheur ici : http://www.oracle-base.com/articles/...Techniques.php

**Farid_92** · 20/01/2009, 14h36

salut,
essaye ca:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
CREATE TABLE TMP_PARENT ("NOM_PARENT" VARCHAR2(20), "ID_ENFANT" NUMBER(10,0));
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT) VALUES ('parent1',1);
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT) VALUES ('parent1',2);
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT) VALUES ('parent1',3);
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT) VALUES ('parent2',4);
 
CREATE TABLE TMP_ENFANT ("ID_ENFANT" NUMBER(10,0), "NOM_ENFANT" VARCHAR2(20));
INSERT INTO TMP_ENFANT (ID_ENFANT,NOM_ENFANT) VALUES (1,'enfant1');
INSERT INTO TMP_ENFANT (ID_ENFANT,NOM_ENFANT) VALUES (2,'enfant2');
INSERT INTO TMP_ENFANT (ID_ENFANT,NOM_ENFANT) VALUES (3,'enfant3');
INSERT INTO TMP_ENFANT (ID_ENFANT,NOM_ENFANT) VALUES (4,'enfant4');
 
SELECT NOM_PARENT,LTRIM(SYS_CONNECT_BY_PATH(NOM_ENFANT,','),',') NOM_ENFANTS
FROM (
  SELECT 
    TMP_PARENT.NOM_PARENT
    ,TMP_ENFANT.NOM_ENFANT
    ,ROW_NUMBER() OVER (PARTITION BY TMP_PARENT.NOM_PARENT ORDER BY TMP_ENFANT.NOM_ENFANT) rn
    ,COUNT(*) OVER (PARTITION BY TMP_PARENT.NOM_PARENT) cnt
  FROM TMP_PARENT
  LEFT JOIN TMP_ENFANT ON TMP_PARENT.ID_ENFANT=TMP_ENFANT.ID_ENFANT
  )
WHERE rn=cnt
START WITH rn=1
CONNECT BY PRIOR NOM_PARENT = NOM_PARENT AND PRIOR rn = rn-1;

Resultat:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
NOM_PARENT           NOM_ENFANTS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
-------------------- -----------------
parent1              enfant1,enfant2,enfant3
parent2              enfant4

---
Farid

**w3blogfr** · 20/01/2009, 15h16

Merci beaucoup, les 2 solutions convenaient, j'ai choisit la 2ième car elle m'évite d'utiliser les trigers(je n'ai pas le droit d'en créer sur le serveur de production).

Une dernière petite question.

En supposant que j'ai un doublon.
Par exemple:

parent1 1
parent1 1

Il me met:

parent1 enfant1,enfant1

Il y a un moyen pour n'avoir qu'une fois enfant1 avec le système start with

ou je dois filtrer dès le départ pour éviter d'avoir le doublon

Cordialement.

**w3blogfr** · 20/01/2009, 15h21

Autant pour moi, j'avais une erreur dans mon select pour identifier mes lignes uniques. Merci beaucoup

**Farid_92** · 20/01/2009, 15h24

La voici avec un filtre:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
CREATE TABLE TMP_PARENT ("NOM_PARENT" VARCHAR2(20), "ID_ENFANT" NUMBER(10,0));
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT) VALUES ('parent1',1);
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT) VALUES ('parent1',2);
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT) VALUES ('parent1',3);
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT) VALUES ('parent2',4);
 
CREATE TABLE TMP_ENFANT ("ID_ENFANT" NUMBER(10,0), "NOM_ENFANT" VARCHAR2(20));
INSERT INTO TMP_ENFANT (ID_ENFANT,NOM_ENFANT) VALUES (1,'enfant1');
INSERT INTO TMP_ENFANT (ID_ENFANT,NOM_ENFANT) VALUES (2,'enfant2');
INSERT INTO TMP_ENFANT (ID_ENFANT,NOM_ENFANT) VALUES (3,'enfant3');
INSERT INTO TMP_ENFANT (ID_ENFANT,NOM_ENFANT) VALUES (4,'enfant4');
 
WITH
  FLT_DATA AS (
    SELECT DISTINCT TMP_PARENT.NOM_PARENT,TMP_ENFANT.NOM_ENFANT
    FROM TMP_PARENT
    LEFT JOIN TMP_ENFANT ON TMP_PARENT.ID_ENFANT=TMP_ENFANT.ID_ENFANT
    )
SELECT NOM_PARENT,LTRIM(SYS_CONNECT_BY_PATH(NOM_ENFANT,','),',') NOM_ENFANTS
FROM (
  SELECT 
    NOM_PARENT
    ,NOM_ENFANT
    ,ROW_NUMBER() OVER (PARTITION BY NOM_PARENT ORDER BY NOM_ENFANT) rn
    ,COUNT(*) OVER (PARTITION BY NOM_PARENT) cnt
  FROM FLT_DATA
  )
WHERE rn=cnt
START WITH rn=1
CONNECT BY PRIOR NOM_PARENT = NOM_PARENT AND PRIOR rn = rn-1;

---
Farid

**salim11** · 20/01/2009, 15h29

Quelle est ta version ?

**Farid_92** · 20/01/2009, 15h31

moi ? 9i

Apriori lui 9i ou + puisqu'elle marche chez lui.
---
Farid

**salim11** · 20/01/2009, 15h37

Salut Farid,

Mais je demande la version de ttropardy.

Oui je sais que ça marche mais coté performance je ne le pense pas .
Salim.

**Farid_92** · 20/01/2009, 15h53

Salut salim,

Si tu as plus performant pour une 9i, je suis preneur

---
Farid

**salim11** · 20/01/2009, 16h05

En 10g.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
SQL> WITH FLT_DATA AS
  2       (SELECT DISTINCT TMP_PARENT.NOM_PARENT, TMP_ENFANT.NOM_ENFANT
  3                   FROM TMP_PARENT LEFT JOIN TMP_ENFANT
  4                        ON TMP_PARENT.ID_ENFANT = TMP_ENFANT.ID_ENFANT
  5                        )
  6  SELECT NOM_PARENT,SUBSTR(RES,2) RES
  7  FROM FLT_DATA
  8  MODEL 
  9  RETURN UPDATED ROWS
 10  PARTITION BY (NOM_PARENT)
 11  DIMENSION BY ( ROW_NUMBER()OVER(PARTITION BY NOM_PARENT ORDER BY NOM_ENFANT) RN)
 12  MEASURES( CAST(NOM_ENFANT  AS VARCHAR2(50)) RES, NOM_ENFANT)
 13  RULES 
 14  UPSERT 
 15  ITERATE (1000)
 16  UNTIL( PRESENTV(NOM_ENFANT[ITERATION_NUMBER+2],1,0)=0)
 17  (RES[0]=RES[0]||'-'||NOM_ENFANT[ITERATION_NUMBER+1]);
 
NOM_PARENT           RES
-------------------- -------------------------------------------------
parent1              enfant1-enfant2-enfant3
parent2              enfant4
 
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=4 Bytes=96)
   1    0   SQL MODEL (ORDERED FAST) (Cost=8 Card=4 Bytes=96)
   2    1     WINDOW (SORT) (Cost=8 Card=4 Bytes=96)
   3    2       VIEW (Cost=8 Card=4 Bytes=96)
   4    3         HASH (UNIQUE) (Cost=8 Card=4 Bytes=200)
   5    4           HASH JOIN (OUTER) (Cost=7 Card=4 Bytes=200)
   6    5             TABLE ACCESS (FULL) OF 'TMP_PARENT' (TABLE) (Cos
          t=3 Card=4 Bytes=100)
 
   7    5             TABLE ACCESS (FULL) OF 'TMP_ENFANT' (TABLE) (Cos
          t=3 Card=4 Bytes=100)
 
 
 
 
SQL> WITH
  2    FLT_DATA AS (
  3      SELECT DISTINCT TMP_PARENT.NOM_PARENT,TMP_ENFANT.NOM_ENFANT
  4      FROM TMP_PARENT
  5      LEFT JOIN TMP_ENFANT ON TMP_PARENT.ID_ENFANT=TMP_ENFANT.ID_ENFANT
  6      )
  7  SELECT NOM_PARENT,LTRIM(SYS_CONNECT_BY_PATH(NOM_ENFANT,','),',') NOM_ENFANTS
  8  FROM (
  9    SELECT 
 10      NOM_PARENT
 11      ,NOM_ENFANT
 12      ,ROW_NUMBER() OVER (PARTITION BY NOM_PARENT ORDER BY NOM_ENFANT) rn
 13      ,COUNT(*) OVER (PARTITION BY NOM_PARENT) cnt
 14    FROM FLT_DATA
 15    )
 16  WHERE rn=cnt
 17  START WITH rn=1
 18  CONNECT BY PRIOR NOM_PARENT = NOM_PARENT AND PRIOR rn = rn-1;
 
NOM_PARENT
--------------------
NOM_ENFANTS
--------------------------------------------------------------------------------
parent1
enfant1,enfant2,enfant3
 
parent2
enfant4
 
 
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=4 Bytes=20
          0)
 
   1    0   TEMP TABLE TRANSFORMATION
   2    1     LOAD AS SELECT
   3    2       HASH (UNIQUE) (Cost=8 Card=4 Bytes=200)
   4    3         HASH JOIN (OUTER) (Cost=7 Card=4 Bytes=200)
   5    4           TABLE ACCESS (FULL) OF 'TMP_PARENT' (TABLE) (Cost=
          3 Card=4 Bytes=100)
 
   6    4           TABLE ACCESS (FULL) OF 'TMP_ENFANT' (TABLE) (Cost=
          3 Card=4 Bytes=100)
 
   7    1     FILTER
   8    7       CONNECT BY (WITH FILTERING)
   9    8         FILTER
  10    9           COUNT
  11   10             VIEW (Cost=3 Card=4 Bytes=200)
  12   11               WINDOW (SORT) (Cost=3 Card=4 Bytes=96)
  13   12                 VIEW (Cost=2 Card=4 Bytes=96)
  14   13                   TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6686
          _FCE1CD56' (TABLE (TEMP)) (Cost=2 Card=4 Bytes=96)
 
  15    8         HASH JOIN
  16   15           CONNECT BY PUMP
  17   15           COUNT
  18   17             VIEW (Cost=3 Card=4 Bytes=200)
  19   18               WINDOW (SORT) (Cost=3 Card=4 Bytes=96)
  20   19                 VIEW (Cost=2 Card=4 Bytes=96)
  21   20                   TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6686
          _FCE1CD56' (TABLE (TEMP)) (Cost=2 Card=4 Bytes=96)
 
  22    8         COUNT
  23   22           VIEW (Cost=3 Card=4 Bytes=200)
  24   23             WINDOW (SORT) (Cost=3 Card=4 Bytes=96)
  25   24               VIEW (Cost=2 Card=4 Bytes=96)
  26   25                 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6686_F
          CE1CD56' (TABLE (TEMP)) (Cost=2 Card=4 Bytes=96)

Resultat d'une requete imbriqué

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager