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)

**Farid_92** · 20/01/2009, 16h11

un cout plus faible en effet

merci pour l'info.
Un jour viendra, je passerai sur 10g....

---
Farid

**w3blogfr** · 20/01/2009, 16h19

La version utilisé actuellement est une 9i,
Je sais que l'entreprise est passé en 10G, mais je crois que ce n'est pas encore le cas sur tout les serveurs, donc je vais pas prendre le risque.
J'ai réussit à adapter la solution à ma requete.

Dans ma table de départ j'avais une notion de population

parent1 enfant1 10
parent1 enfant2 2
parent1 enfant3 1

Il me retourne bien les enfants concaténé
parent1 enfant1,enfant2,enfant3 1

Mais la population correspond à la dernière ligne, comment en faire un count?

**w3blogfr** · 21/01/2009, 11h56

j'ai cherché un peu partout, au moins j'ai découvert des trucs sur oracle, mais en revanche, j'ai toujours comment faire une somme dans mon cas.
Quelqu'un a une idée?

D'avance merci

**Farid_92** · 21/01/2009, 12h01

c'est un count ou une somme que tu veux faire ?

---
Farid

**w3blogfr** · 21/01/2009, 12h02

UNe somme de mes 3 populations.

**Farid_92** · 21/01/2009, 12h10

Ca me parait etrange ce concept de population a ce niveau ?
C'est le nombre de personne habitant le foyer ?
Regarde si ca te va:

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
 
CREATE TABLE TMP_PARENT ("NOM_PARENT" VARCHAR2(20), "ID_ENFANT" NUMBER(10,0),"POPULATION" NUMBER(10,0));
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT,POPULATION) VALUES ('parent1',1,10);
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT,POPULATION) VALUES ('parent1',2,2);
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT,POPULATION) VALUES ('parent1',3,1);
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT,POPULATION) VALUES ('parent2',4,3);
 
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
    ),
  FLT_JOIN AS (
    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
    )
SELECT FLT_JOIN.NOM_PARENT,FLT_JOIN.NOM_ENFANTS,SUM(POPULATION)
FROM FLT_JOIN
JOIN TMP_PARENT ON FLT_JOIN.NOM_PARENT=TMP_PARENT.NOM_PARENT
GROUP BY FLT_JOIN.NOM_PARENT,FLT_JOIN.NOM_ENFANTS;

---
Farid

**w3blogfr** · 21/01/2009, 12h28

En fait, la notion de population est un truc hérité de mon précédent select.
Il faudrait voir ça comme le nombre de enfants de l'enfant lui même.

On aurait donc

Le parent, la liste de ses enfants Le nombre de ces petits enfants

Comment fonctionne SYS_CONNECT_BY_PATH, il va bien chercher les différentes lignes pour les concatener? Il n'y a pas une fonction pour additionner au lieu de concatener?

**Farid_92** · 21/01/2009, 12h36

montre nous ton "précédent select" que tu utilises.

---
Farid

**w3blogfr** · 21/01/2009, 13h30

Resultat d'une requete imbriqué

SQL Oracle

Discussions similaires

Partager

Partager