Beegee | Je crois que j'ai la requête qu'il te faut :
Code :
- DROP TABLE matable
- Table dropped
- CREATE TABLE matable
- (pere VARCHAR(20),
- fils VARCHAR(20),
- qte NUMBER(9))
- Table created
- INSERT INTO matable VALUES ('A', 'B', 3)
- 1 row inserted
- INSERT INTO matable VALUES ('A', 'C', 11)
- 1 row inserted
- INSERT INTO matable VALUES ('A', 'D', 5)
- 1 row inserted
- INSERT INTO matable VALUES ('B', 'F', 4)
- 1 row inserted
- INSERT INTO matable VALUES ('G', 'I', 7)
- 1 row inserted
- INSERT INTO matable VALUES ('J', 'K', 8)
- 1 row inserted
- INSERT INTO matable VALUES ('J', 'L', 9)
- 1 row inserted
- INSERT INTO matable VALUES ('K', 'M', 2)
- 1 row inserted
- INSERT INTO matable VALUES ('K', 'N', 1)
- 1 row inserted
- COMMIT
- Commit complete
- SELECT * FROM matable
- PERE FILS QTE
- -------------------- -------------------- ----------
- A B 3
- A C 11
- A D 5
- B F 4
- G I 7
- J K 8
- J L 9
- K M 2
- K N 1
- 9 rows selected
- SELECT fils,
- (SELECT EXP(SUM(LN(qte)))
- FROM matable t2
- START WITH t2.fils = t1.fils
- CONNECT BY PRIOR t2.pere = t2.fils ) qte_totale
- FROM (SELECT DISTINCT fils
- FROM matable
- CONNECT BY PRIOR fils = pere) t1
- WHERE NOT EXISTS (SELECT NULL FROM matable t3 WHERE t3.pere = t1.fils)
- FILS QTE_TOTALE
- -------------------- ----------
- C 11
- D 5
- F 12
- I 7
- L 9
- M 16
- N 8
- 7 rows selected
|
|
