Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Membre du Club
    Homme Profil pro
    Inscrit en
    juin 2008
    Messages
    53
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : juin 2008
    Messages : 53
    Points : 48
    Points
    48

    Par défaut Requête récursive pour obtenir le chemin à partir d'une arborescence

    Bonjour.

    J'ai la table suivante:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE "project" (
    	"idobject" serial,
    	"idparent" integer,
    	"project_name" text,
    	PRIMARY key (idobject),
    	FOREIGN key (idparent) REFERENCES project (idobject)
    ) INHERITS (object);

    Je cherche à obtenir le chemin des arborescences de projet.

    Actuellement je fais ça :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    SELECT n1.idobject, 
    	CASE WHEN n3.project_name IS NULL THEN '' ELSE n3.project_name || ' / ' END
    	|| CASE WHEN n2.project_name IS NULL THEN '' ELSE n2.project_name || ' / ' END 
    	|| n1.project_name AS project_path FROM project AS n1
    LEFT OUTER JOIN project AS n2 ON n1.idparent = n2.idobject
    LEFT OUTER JOIN project AS n3 ON n2.idparent = n3.idobject
    ORDER BY project_path;

    et j'obtiens

     idobject |      project_path       
    ----------+-------------------------
      7546762 | prj1
      7546770 | prj1 / prj1.1
      7546772 | prj1 / prj1.2
      7546766 | prj2
    
    Ce qui est exactement ce que je veux, sauf que j'aurais voulu faire ça avec une requête récursive afin que cela fonctionne quel que soit le nombre de niveaux (ce qui n'est pas le cas ici).

    Est-ce possible ?

  2. #2
    ced
    ced est actuellement connecté
    Rédacteur/Modérateur

    Avatar de ced
    Homme Profil pro
    Gestion de bases de données techniques
    Inscrit en
    avril 2002
    Messages
    5 781
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Loiret (Centre)

    Informations professionnelles :
    Activité : Gestion de bases de données techniques
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : avril 2002
    Messages : 5 781
    Points : 22 972
    Points
    22 972

    Par défaut

    Bonjour,

    Oui, c'est possible à l'aide d'une requête récursive.
    Tout est expliqué ici : http://sqlpro.developpez.com/cours/s...te-recursives/
    Ça fonctionne également sous PostgreSQL.
    Rédacteur / Modérateur SGBD et R
    Mes tutoriels et la FAQ MySQL

    ----------------------------------------------------
    Pensez aux balises code et au tag
    Une réponse vous a plu ? N'hésitez pas à y mettre un
    Je ne réponds pas aux questions techniques par message privé, les forums sont là pour ça

  3. #3
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 210
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 210
    Points : 42 579
    Points
    42 579

    Par défaut

    Oui...

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    WITH RECURSIVE
    T0 AS
    (SELECT idobject AS id_racine,  project_name
     FROM   project
     WHERE  idparent IS NULL --> les racines
     UNION ALL
     SELECT T0.id_racine, CONCAT(T0.project_name, ' / ', T1.project_name)
     FROM   T0
            JOIN project AS T1
                 ON T0.id_racine = T1.idparent)
    SELECT *
    FROM   T0;
    mais vous gagneriez notablement en performances en utilisation une représentation intervallaire de votre arborescence.
    Lisez les article que j'ai écrit à ce sujet :
    http://sqlpro.developpez.com/cours/arborescence/
    https://blog.developpez.com/sqlpro/p...vallaire_proce
    https://blog.developpez.com/sqlpro/p...edure_de_depla
    https://blog.developpez.com/sqlpro/p...edure_de_derec
    https://blog.developpez.com/sqlpro/p...-intervallaire
    https://blog.developpez.com/sqlpro/p...-intervallaire

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  4. #4
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 210
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 210
    Points : 42 579
    Points
    42 579

    Par défaut

    Citation Envoyé par ced Voir le message
    Bonjour,

    Oui, c'est possible à l'aide d'une requête récursive.
    Tout est expliqué ici : http://sqlpro.developpez.com/cours/s...te-recursives/
    Ça fonctionne également sous PostgreSQL.
    Avec une petite différence : le mot clef RECURSIVE n'est pas obligatoire dans la norme, mais SQL Server l'impose....

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  5. #5
    Membre du Club
    Homme Profil pro
    Inscrit en
    juin 2008
    Messages
    53
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : juin 2008
    Messages : 53
    Points : 48
    Points
    48

    Par défaut

    Merci.

    La requête proposée doit sans doute fonctionner car il ne semble pas y avoir de problème syntaxique,
    Mais après avoir mis à plat mon serveur pendant 2 minutes, j'ai décidé de l'interrompre !

    pourtant il n'y a que 20 enregistrement dans ma table project !
    Je suppose qu'il doit y avoir un truc qui boucle à l'infini !

  6. #6
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 210
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 210
    Points : 42 579
    Points
    42 579

    Par défaut

    C'est possible... Vu que tu ne nous a pas donné un jeu d'essais pour tester, j'ai testé dans le vide.

    maintenant si tu respecte la charte de postage en nous donnant des :
    INSERT INTO ...
    alors on pourra tester.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  7. #7
    Membre du Club
    Homme Profil pro
    Inscrit en
    juin 2008
    Messages
    53
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : juin 2008
    Messages : 53
    Points : 48
    Points
    48

    Par défaut

    autant pour moi

    CREATE TABLE project (
        idobject serial,
        idparent integer,
        project_name text,
    )
    
    INSERT INTO project VALUES (7546762, NULL, 'Bureau Virtuel');
    INSERT INTO project VALUES (7546770, 7546762, 'Agenda');
    INSERT INTO project VALUES (7546772, 7546762, 'Kernel');
    INSERT INTO project VALUES (7547715, 7546762, 'Gestionnaire de projet');
    INSERT INTO project VALUES (7547750, NULL, 'Serveur');
    INSERT INTO project VALUES (7547794, 7547750, 'Scripts');
    INSERT INTO project VALUES (7547795, 7547750, 'Administration');
    INSERT INTO project VALUES (7547796, NULL, 'Services Utilisateurs');
    INSERT INTO project VALUES (7547797, 7547796, 'Support');
    INSERT INTO project VALUES (7547798, 7547796, 'E-procurement');
    INSERT INTO project VALUES (7547799, 7547796, 'Appel d''offre');
    INSERT INTO project VALUES (7546766, NULL, 'Client');
    INSERT INTO project VALUES (7547890, 7546766, 'Support');
    INSERT INTO project VALUES (7547891, 7546766, 'Développement');
    
    Il n'y a même que 14 lignes.

    Je te donne tout ça pour voir comment on fait, mais finalement je vais précalculer le chemin complet.
    Ca sera plus rapide !

  8. #8
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 210
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 210
    Points : 42 579
    Points
    42 579

    Par défaut

    Petite correction :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    WITH RECURSIVE
    T0 AS
    (SELECT idobject AS id_racine,  project_name
     FROM   project
     WHERE  idparent IS NULL --> les racines
     UNION ALL
     SELECT T1.idobject, CONCAT(T0.project_name, ' / ', T1.project_name)
     FROM   T0
            JOIN project AS T1
                 ON T0.id_racine = T1.idparent)
    SELECT *
    FROM   T0
    Et pour avoir uniquement les lignes "finales" :

    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
    WITH RECURSIVE
    T0 AS
    (SELECT idobject  AS id_racine,  idobject AS id_object, project_name, 0 AS etape
     FROM   project
     WHERE  idparent IS NULL --> les racines
     UNION ALL
     SELECT T0.id_racine, T1.idobject, CONCAT(T0.project_name, ' / ', T1.project_name), etape + 1
     FROM   T0
            JOIN project AS T1
                 ON T0.id_object = T1.idparent),
    T1 AS
    (SELECT *, MAX(etape) OVER(PARTITION BY id_racine) AS last_etape
     FROM   T0)
    SELECT id_racine, project_name AS project_path 
    FROM   T1
    WHERE  etape = last_etape
    ORDER  BY 1
    À me lire : http://sqlpro.developpez.com/cours/s...te-recursives/

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  9. #9
    Membre du Club
    Homme Profil pro
    Inscrit en
    juin 2008
    Messages
    53
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : juin 2008
    Messages : 53
    Points : 48
    Points
    48

    Par défaut

    Ah ouais. ça fonctionne.
    Merci.

    Ca retourne pas le bon ID (moi j'aurai voulu l'ID de l'élément le plus bas)
    Mais je devrais être capable de corriger moi-même.

  10. #10
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    4 948
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 4 948
    Points : 10 155
    Points
    10 155

    Par défaut

    Bonjour,

    Citation Envoyé par SQLpro Voir le message
    Avec une petite différence : le mot clef RECURSIVE n'est pas obligatoire dans la norme, mais SQL Server l'impose....
    Sans doute voulais-tu dire que PostGre l'impose (SQL Server lui, la refuse, dans tous les cas).

    Par ailleurs, la norme ne l'impose pas pour la déclaration d'une CTE,

    <with clause> ::= WITH [ RECURSIVE ] <with list>
    mais s'il n'est pas précisé, alors la CTE ne peut être récursive :
    a) If a <with clause> WC immediately contains RECURSIVE, then WC and its <with list
    element>s are said to be potentially recursive. Otherwise they are said to be non-recursive.
    b) Let n be the number of <with list element>s and let WLEi and WLEj be the i-th and j-th
    <with list element>s for every (i,j) with i ranging from 1 (one) to n and j ranging from i+1
    to n. If WLEi is not potentially recursive, then it shall not immediately contain the <query
    name> immediately contained in WLEj.
    c) If the <with clause> is non-recursive, then for all i between 1 (one) and n, the scope of the
    <query name> WQN immediately contained in WLEi is the <query expression> immediately
    contained in every <with list element> WLEk, where k ranges from i+1 to n, and the <query
    expression body> immediately contained in <query expression>. A <table or query name>
    contained in this scope that immediately contains WQN is a query name in scope.
    d) If the <with clause> is potentially recursive, then for all i between 1 (one) and n, the scope
    of the <query name> WQN immediately contained in WLEi is the <query expression>
    immediately contained in every <with list element> WLEk, where k ranges from 1 (one) to
    n, and the <query expression body> immediately contained in <query expression>. A <table
    or query name> contained in this scope that immediately contains WQN is a query name in
    scope.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Obtenir un dump sql à partir d'une requête
    Par F2000 dans le forum Requêtes
    Réponses: 1
    Dernier message: 22/04/2011, 06h11
  2. [Débutant] Fonction prédéfinie pour obtenir le chemin absolu d'un fichier ?
    Par hellfoust dans le forum MATLAB
    Réponses: 8
    Dernier message: 11/09/2010, 15h47
  3. Réponses: 4
    Dernier message: 06/03/2009, 17h39
  4. Requête SQL pour obtenir les intitulés des champs d'une table
    Par Zakapatul dans le forum MS SQL-Server
    Réponses: 2
    Dernier message: 01/12/2008, 10h51
  5. Quelle requête SQL pour obtenir mon résultat ?
    Par nicolas.pied dans le forum Requêtes
    Réponses: 2
    Dernier message: 03/02/2007, 10h58

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo