Requête hiérarchiques ?

Version imprimable

30/07/2007, 14h04
Octooop

Requête hiérarchiques ?

Bonjour à tous,

Je débute et viens de m'inscrire,
Je parcours l'ensemble des sites depuis quelques jours, mais je bloque ...

J'ai dans la table "TEST" les informations suivantes :

ID ID_LINE REF
X 1 A
X 2 B
Y 2 A
Y 1 C
Z 3 C
Z 4 D
W 1 E
W 2 F

Nous remarquons que :
- à un ID donné (X par exemple) est rattaché 2 références (A et B) ...
=> Les ID permettent de lier les REF entre elles
- Nous remarquons également que l'ID Y lie les REF A et C
=> la référence A se trouve également pour l'ID "X", donc les ID X et Y vont également ensemble
- ...

je souhaite obtenir via un Insert SELECT, les informations suivantes dans une nouvelle table :

REF ID_UNIQUE
A X
B X
C X
D X
E W
F W

L'ensemble des références (A, B, C, D) vont en fin de compte ensemble, j'insert dnas le champ "ID_UNIQUE" un seul ID (n'importe lequel) me permettant de lier les 4 références.

Je me suis interressé au connect by, mais .... mes compétences viennent à leurs limites, pouvez vous m'aider ?

Code:

1
2
3
4
5
 
insert into new_test (id,ref)
select ID,REF
from test
group by id,ref

30/07/2007, 14h35
Octooop

merci Garuda pour la rapidité de ta réponse, mais j'ai bien peur que cela ne fonctionne pas, cela me donne ceci :

REF ID_UNIQUE
A X
B X
A Y
C Y
C Z
D Z

Alors que je souhaite obtenir un ID unique pour toutes les références qui vont ensembles.

Code:

1
2
3
 
select REF,(select id from test t2 where test.ref=t2.ref  and rownum=1) 
from test

30/07/2007, 15h25
McM

Ca ne marchera pas non plus Garuda.
Faut lire l'exemple ;)

C : Y
Or B: Y et B:X => C:X (alors que C:X n'existe pas).

Ca a l'air un peu complic comme truc à faire en 1 requete.
Je vais regarder
30/07/2007, 15h26
Garuda

Oui mais j'ai rien compris aux specs !!!:?
30/07/2007, 15h41
miloux32

En gros tu cherches une regle de transitivité ?

select REF, MAX (ID) from table a
where (je sais pas exactement ce que tu veux ...)
30/07/2007, 15h41
McM
Je simplifie :
Code:

1 2 3 4 5 6 A 1 A 2 B 1 C 2 C 3 D 3
A et B on en commun la valeur 1 => A=B=1
C et A on en commun une valeur (2) => C=A=2 mais A=B=1 => A=B=C = 1
C et D on en commun une valeur (3) => C=D=3 mais C=A=1 => A=B=C=D=1

Le problème c'est que pour faire ça en SQL, c'est chaud.
Faudrait faire une copie de la table et faire des updates je pense.
30/07/2007, 15h47
Octooop

Voila, c'est tout a fait ca le problème ...
Ca me rassure que vous trouviez ca complexe ... :D

C'est bon, j'ai trouvé.
J'ai laissé toutes les colonnes pour comprendre le cheminement

Code:

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
WITH T AS ( 
		SELECT 'X' ID, 'A' REF FROM DUAL
		UNION ALL
		SELECT 'X' ID, 'B' REF FROM DUAL
		UNION ALL
		SELECT 'Y' ID, 'A' REF FROM DUAL
		UNION ALL
		SELECT 'Y' ID, 'C' REF FROM DUAL
		UNION ALL
		SELECT 'Z' ID, 'D' REF FROM DUAL
		UNION ALL
		SELECT 'Z' ID, 'E' REF FROM DUAL
		UNION ALL
		SELECT 'W' ID, 'E' REF FROM DUAL
		UNION ALL
		SELECT 'X' ID, 'F' REF FROM DUAL
		), 
tid AS (SELECT ID, MIN(REF) AS minref
		FROM T
		GROUP BY ID),
tref AS (SELECT REF, MIN(ID) AS minid
		FROM T
		GROUP BY REF)
SELECT REF, ID, 
		(SELECT minref FROM tid WHERE tid.ID = t.ID) "1st_ref",
		(SELECT minid FROM tref, tid WHERE tid.ID = t.ID AND minref = tref.REF) AS premier_id
FROM T

Code:

1
2
3
4
5
6
7
8
9
REF	ID	1st_REF	PREMIER_ID
A	X	A	X
B	X	A	X
A	Y	A	X
C	Y	A	X
D	Z	D	Z
E	Z	D	Z
E	W	E	W
F	X	A	X

Tu n'as besoin que des colonnes ref et premier_id.
Par contre les with sont obligés.

30/07/2007, 17h25
Octooop

Et en plus, ca marche ... Merci,

Mais du coup, voila ce que je fais :
- Je crée une première table ou je stock :
tid AS (SELECT ID, MIN(REF) AS minref
FROM T
GROUP BY ID),

- Une seconde ou je stock :
tref AS (SELECT REF, MIN(ID) AS minid
FROM T
GROUP BY REF)

Et j'execute :
SELECT REF,
(SELECT minid FROM tref, tid WHERE tid.ID = t.ID AND minref = tref.REF) AS premier_id
FROM T

Et du coup, c'est peut etre parce qu'on est Lundi, mais cela ne marche pas ...
Quand tu dis, les with sont obligés ? je peux tout de même faire comme décris plus haut ?

Citation:

Envoyé par Octooop

Et en plus, ca marche ... Merci,
Et du coup, c'est peut etre parce qu'on est Lundi, mais cela ne marche pas ...

1/ Il faut toujours mettre la balise CODE sur vos codes.. (le # dans l'éditeur de message).

2/ Ca ne marche pas.. ca va pas nous aider à trouver ce qui ne marche pas
(à mon avis faut juste que tu rajoutes un DISTINCT vu qu'on est par ligne de départ.)

3/ Vu que tu n'as pas donné ta version de base, mais que t'as l'air de connaitre le WITH, tu pourrais être en 10g. Donc pourquoi ne pas laisser les WITH tid et tref pour ton select d'insert ?
Code:

1 2 3 4 5 6 7 8 9 INSERT INTO NOUVELLE_TABLE(col1, col2) WITH tid AS (SELECT ID, MIN(REF) AS minref FROM T GROUP BY ID), tref AS (SELECT REF, MIN(ID) AS minid FROM T GROUP BY REF) SELECT DISTINCT REF, (SELECT minid FROM tref, tid WHERE tid.ID = t.ID AND minref = tref.REF) AS premier_id FROM T

30/07/2007, 17h33
McM

Et oui, tu peux te passer des WITH en stockant dans tes tables temporaires..
Mais c'est moins pur.
Quelqu'un pourrait faire des modifs entre les insert et le select de fin ;)
Sauf qu'en 8 t'es obligé de faire comme tu dis.

Je note pour les balises,
Effectivement je suis en 10 donc le with me va bien,

Voila le cas qui ne "marche pas",

Code:

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
WITH T AS ( 
        SELECT 'A' ID, '1' REF FROM DUAL
        UNION ALL
        SELECT 'A' ID, '2' REF FROM DUAL
        UNION ALL
        SELECT 'B' ID, '1' REF FROM DUAL
        UNION ALL
        SELECT 'B' ID, '3' REF FROM DUAL
        UNION ALL
        SELECT 'C' ID, '4' REF FROM DUAL
        UNION ALL
        SELECT 'C' ID, '1' REF FROM DUAL
        UNION ALL
        SELECT 'D' ID, '4' REF FROM DUAL
        UNION ALL
  SELECT 'D' ID, '5' REF FROM DUAL    )
        , 
tid AS (SELECT ID, MIN(REF) AS minref
        FROM T
        GROUP BY ID),
tref AS (SELECT REF, MIN(ID) AS minid
        FROM T
        GROUP BY REF)
SELECT REF, ID, 
        (SELECT minref FROM tid WHERE tid.ID = t.ID),
        (SELECT minid FROM tref, tid WHERE tid.ID = t.ID AND minref = tref.REF) AS premier_id
FROM T

Merci de tes réponses,
Pour tout t'avouer, je suis assez bluffé. :king:

31/07/2007, 09h38
McM

T'aurais pu dire pourquoi ça marchait pas..
Mais je crains que tu n'aies raison : Marche pas ma requete.
Je vais voir si une fonction récursive pourrait marcher.

Finalement faut passer par du pl et boucler.

Code:

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 WMCT ( ID VARCHAR2(1), REF VARCHAR2(1), id2 VARCHAR2(1))
TABLESPACE DATA
 
 
INSERT INTO wmct(ID, REF)
        SELECT 'A' ID, '1' REF FROM DUAL
        UNION ALL
        SELECT 'A' ID, '2' REF FROM DUAL
        UNION ALL
        SELECT 'B' ID, '1' REF FROM DUAL
        UNION ALL
        SELECT 'B' ID, '4' REF FROM DUAL
        UNION ALL
        SELECT 'C' ID, '4' REF FROM DUAL
        UNION ALL
        SELECT 'C' ID, '3' REF FROM DUAL
        UNION ALL
        SELECT 'D' ID, '4' REF FROM DUAL
        UNION ALL
  		SELECT 'D' ID, '5' REF FROM DUAL   
COMMIT;
 
UPDATE wmct SET id2 = ID
 
COMMIT;
 
UPDATE wmct t1
SET id2 = (SELECT MIN(id2) FROM wmct t2 WHERE t2.REF = t1.REF)
 
BEGIN
	LOOP
		UPDATE wmct t1
		SET id2 = (SELECT MIN(LEAST(id2, ID)) FROM wmct t2 WHERE t2.ID = t1.ID2)
		WHERE id2 <>(SELECT MIN(LEAST(id2, ID)) FROM wmct t2 WHERE t2.ID = t1.ID2);
		EXIT WHEN SQL%rowcount = 0;
	END LOOP;
END;

Merci MCM,

Bon je vais être tres lourd :oops: , mais en fait le passage par un min génère des erreurs.

Dans ce cas par exemple :

Code:

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
CREATE TABLE WMCT ( ID VARCHAR2(2), REF VARCHAR2(2), id2 VARCHAR2(2))
INSERT INTO wmct(ID, REF)
        SELECT 'U5' ID, 'F3' REF FROM DUAL
        UNION ALL
        SELECT 'U5' ID, 'C1' REF FROM DUAL
        UNION ALL
        SELECT 'U6' ID, 'F3' REF FROM DUAL
        UNION ALL
        SELECT 'U6' ID, 'A1' REF FROM DUAL
        UNION ALL
        SELECT 'U3' ID, 'F1' REF FROM DUAL
        UNION ALL
        SELECT 'U3' ID, 'A1' REF FROM DUAL
        UNION ALL
        SELECT 'U4' ID, 'C1' REF FROM DUAL
        UNION ALL
        SELECT 'U4' ID, 'A2' REF FROM DUAL   
        UNION ALL
        SELECT 'U2' ID, 'C1' REF FROM DUAL
        UNION ALL
        SELECT 'U2' ID, 'F2' REF FROM DUAL  ;
          COMMIT;
UPDATE wmct SET id2 = ID;
COMMIT;
UPDATE wmct t1
SET id2 = (SELECT MIN(id2) FROM wmct t2 WHERE t2.REF = t1.REF);
COMMIT;
BEGIN
    LOOP
        UPDATE wmct t1
        SET id2 = (SELECT MIN(LEAST(id2, ID)) FROM wmct t2 WHERE t2.ID = t1.ID2)
        WHERE id2 <>(SELECT MIN(LEAST(id2, ID)) FROM wmct t2 WHERE t2.ID = t1.ID2);
        EXIT WHEN SQL%rowcount = 0;
    END LOOP;
END;
COMMIT;

meuh non t'es pas lourd.. c'est juste un peu chiant à coder.

Faut 1 variable et 3 requetes

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
LOOP
 v_nb := 0;
UPDATE wmct t1
        SET id2 = (SELECT MIN(LEAST(id2, ID)) FROM wmct t2 WHERE t2.ID = t1.ID2)
        WHERE id2 <>(SELECT MIN(LEAST(id2, ID)) FROM wmct t2 WHERE t2.ID = t1.ID2);
v_nb := sql%rowcount;
 
        UPDATE wmct t1
        SET id2 = (SELECT MIN(LEAST(id2, ID)) FROM wmct t2 WHERE t2.ID = t1.ID)
        WHERE id2 <>(SELECT MIN(LEAST(id2, ID)) FROM wmct t2 WHERE t2.ID = t1.ID);
v_nb := v_nb + sql%rowcount;
 
        UPDATE wmct t1
        SET id2 = (SELECT MIN(LEAST(id2, ID)) FROM wmct t2 WHERE t2.REF = t1.REF)
        WHERE id2 <>(SELECT MIN(LEAST(id2, ID)) FROM wmct t2 WHERE t2.REF = t1.REF);
v_nb := v_nb + sql%rowcount;
 
EXIT when v_nb= 0;
END LOOP;

02/08/2007, 13h49
Octooop

:yaisse2: merci McM :yaisse2:

Ca marche au top ... 8-)
Maintenant, faut que j'essaye de l'optimiser pour pouvoir traiter un volume de 10000 lignes.