Discussion :

[rhizome7]

Bonjour !

J'ai cree un arbre comme explique sur cet article :
http://sqlpro.developpez.com/cours/arborescence/



Ca fonctionne à merveille. Mais j'ai une requête qui me semble impossible à faire...

Je veux récupérer les noeuds et les feuilles "fils / filles" directements liées au noeud "Terrestre" (père), càd :

• Moto
• Camion
• Voiture
• Vélo

mais PAS Trail et Side-car (feuilles filles de "Moto").

Est-ce réalisable avec la representation intervallaire ?

Merci pour votre aide !

[Oishiiii]

Bonjour,

C'est possible, mais pour cela il faut compter les niveaux de chaque éléments de la branche.

Essayez ceci :

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
SELECT 	noeud.nfm_lib,
		(COUNT(parent.nfm_lib) - (td.niveau + 1)) AS niveau
FROM new_famille AS noeud
	JOIN new_famille AS parent
		ON noeud.bg BETWEEN parent.bg AND parent.bd
	JOIN new_famille AS enfant
		ON noeud.bg BETWEEN enfant.bg AND enfant.bd
	JOIN (
		SELECT noeud.nfm_lib, (COUNT(parent.nfm_lib) - 1) AS niveau
		FROM new_famille AS noeud, 
			JOIN new_famille AS parent
				ON noeud.bg BETWEEN parent.bg AND parent.bd
		WHERE noeud.nfm_lib = 'Terrestre'
		GROUP BY noeud.nfm_lib
	) AS td
		ON td.nfm_lib = enfant.nfm_lib
GROUP BY noeud.nfm_lib
HAVING niveau = 1

[SQLpro]

Oulala, c'est lourd !!!!

Bonjour,

C'est possible, mais pour cela il faut compter les niveaux de chaque éléments de la branche.

Essayez ceci :

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
SELECT 	noeud.nfm_lib,
		(COUNT(parent.nfm_lib) - (td.niveau + 1)) AS niveau
FROM new_famille AS noeud
	JOIN new_famille AS parent
		ON noeud.bg BETWEEN parent.bg AND parent.bd
	JOIN new_famille AS enfant
		ON noeud.bg BETWEEN enfant.bg AND enfant.bd
	JOIN (
		SELECT noeud.nfm_lib, (COUNT(parent.nfm_lib) - 1) AS niveau
		FROM new_famille AS noeud, 
			JOIN new_famille AS parent
				ON noeud.bg BETWEEN parent.bg AND parent.bd
		WHERE noeud.nfm_lib = 'Terrestre'
		GROUP BY noeud.nfm_lib
	) AS td
		ON td.nfm_lib = enfant.nfm_lib
GROUP BY noeud.nfm_lib
HAVING niveau = 1

Le mieux est de stocker le niveau avec les bornes gauches et droite en mode intervallaire, comme je le suggère dans mon article http://sqlpro.developpez.com/cours/arborescence/#L2.14

Dès lors avec la colonne NFM_NV (niveau), la requête s'écrit :

Code :

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

1
2
3
4
5
6
7
SELECT Tout.*
FROM   NEW_FAMILLE AS Tout
       INNER JOIN NEW_FAMILLE AS Tin
             ON     Tout.NFM_BG > Tin.NFM_BG
                AND Tout.NFM_BD < Tin.NFM_BD
                AND Tout.NFM_NV = Tin.NFM_NV + 1 --> descendant de niveau 1
WHERE  Tin.NFM_LIB = 'Terrestre'  --> noeud de référence

A +

[fsmrel]

Bonsoir,


Une variante.

Si votre SGBD sait traiter de la récursivité, une technique générale peut consister à calculer la fermeture transitive d’une nomenclature, puis l’exploiter pour les cas particuliers. Voyez par exemple la discussion avec antonomas : Représentation d'arbres entremêlés.

Remplaçons-y ENTREPRISE par MOYEN (sous-entendu de locomotion) et ACTIONNAIRE par NOMENCLATURE.

Création des tables :

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
CREATE TABLE MOYEN
(
        MoyId    Char(6)      NOT NULL
      , MoyNom   Varchar(32)  NOT NULL
    , CONSTRAINT MOYEN_PK PRIMARY KEY (MoyId)
) ;
CREATE TABLE NOMENCLATURE
(
        EnfantId    Char(6)      NOT NULL
      , ParentId    Char(6)      NOT NULL
    , CONSTRAINT NOMENCL_PK PRIMARY KEY (EnfantId, ParentId)
    , CONSTRAINT NOMENCL_FK1 FOREIGN KEY (EnfantId) REFERENCES MOYEN (MoyId)
    , CONSTRAINT NOMENCL_FK2 FOREIGN KEY (ParentId) REFERENCES MOYEN (MoyId)
    , CONSTRAINT NOMENCL_CHK1 CHECK (EnfantId <> ParentId)
) ;

Alimentation de la table MOYEN :

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
INSERT INTO MOYEN VALUES ('e01', 'transport') ;
INSERT INTO MOYEN VALUES ('e02', 'marin') ;
INSERT INTO MOYEN VALUES ('e03', 'terrestre') ;
INSERT INTO MOYEN VALUES ('e04', 'aérien') ;
INSERT INTO MOYEN VALUES ('e05', 'voilier') ;
INSERT INTO MOYEN VALUES ('e06', 'paquebot') ;
INSERT INTO MOYEN VALUES ('e07', 'planche à voile') ;
INSERT INTO MOYEN VALUES ('e08', 'moto') ;
INSERT INTO MOYEN VALUES ('e09', 'camion') ;
INSERT INTO MOYEN VALUES ('e10', 'voiture') ;
INSERT INTO MOYEN VALUES ('e11', 'vélo') ;
INSERT INTO MOYEN VALUES ('e12', 'avion') ;
INSERT INTO MOYEN VALUES ('e13', 'uml') ;
INSERT INTO MOYEN VALUES ('e14', 'fusée') ;
INSERT INTO MOYEN VALUES ('e15', 'hélicoptère') ;
INSERT INTO MOYEN VALUES ('e16', 'parachute') ;
INSERT INTO MOYEN VALUES ('e17', 'planeur') ;
INSERT INTO MOYEN VALUES ('e18', 'trail') ;
INSERT INTO MOYEN VALUES ('e19', 'side-car') ;
INSERT INTO MOYEN VALUES ('e20', 'civil') ;
INSERT INTO MOYEN VALUES ('e21', 'tourisme') ;
INSERT INTO MOYEN VALUES ('e22', 'militaire') ;

Alimentation de la table NOMENCLATURE :

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
INSERT INTO NOMENCLATURE VALUES ('e02', 'e01') ;
INSERT INTO NOMENCLATURE VALUES ('e03', 'e01') ;
INSERT INTO NOMENCLATURE VALUES ('e04', 'e01') ;
INSERT INTO NOMENCLATURE VALUES ('e05', 'e02') ;
INSERT INTO NOMENCLATURE VALUES ('e06', 'e02') ;
INSERT INTO NOMENCLATURE VALUES ('e07', 'e02') ;
INSERT INTO NOMENCLATURE VALUES ('e08', 'e03') ;
INSERT INTO NOMENCLATURE VALUES ('e09', 'e03') ;
INSERT INTO NOMENCLATURE VALUES ('e10', 'e03') ;
INSERT INTO NOMENCLATURE VALUES ('e11', 'e03') ;
INSERT INTO NOMENCLATURE VALUES ('e12', 'e04') ;
INSERT INTO NOMENCLATURE VALUES ('e13', 'e04') ;
INSERT INTO NOMENCLATURE VALUES ('e14', 'e04') ;
INSERT INTO NOMENCLATURE VALUES ('e15', 'e04') ;
INSERT INTO NOMENCLATURE VALUES ('e16', 'e04') ;
INSERT INTO NOMENCLATURE VALUES ('e17', 'e04') ;
INSERT INTO NOMENCLATURE VALUES ('e18', 'e08') ;
INSERT INTO NOMENCLATURE VALUES ('e19', 'e08') ;
INSERT INTO NOMENCLATURE VALUES ('e20', 'e12') ;
INSERT INTO NOMENCLATURE VALUES ('e21', 'e12') ;
INSERT INTO NOMENCLATURE VALUES ('e22', 'e12') ;

Création d’une vue récursive avec laquelle on amorce la pompe avec la racine 'e01' (transport) :

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
CREATE VIEW DESCENDANCE (Parent, Enfant)
AS
WITH DESCENDANCE (ParentId, EnfantId) AS
(
 (
  SELECT   ParentId, EnfantId
  FROM     NOMENCLATURE
  WHERE    ParentId = 'e01'
 )
UNION ALL
 (SELECT   x.ParentId, x.EnfantId
  FROM     NOMENCLATURE AS x JOIN DESCENDANCE AS y
             ON y.EnfantId = x.ParentId
 )
)
SELECT DISTINCT z.MoyNom AS Parent, y.MoyNom AS Enfant 
FROM   DESCENDANCE AS x 
         JOIN MOYEN AS y ON x.EnfantId = y.MoyId
         JOIN MOYEN AS z ON x.ParentId = z.MoyId ;

Pour connaître les moyens directement rattachés au moyen 'terrestre' et eux seuls :

Code :

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

1
2
3
SELECT DISTINCT *
FROM   DESCENDANCE
WHERE Parent = 'terrestre' ;