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

SQLite Discussion :

Requête récursive


Sujet :

SQLite

  1. #1
    Nouveau membre du Club
    Requête récursive
    Bonjour

    je cherche a faire une requête recursive, j explique
    j ai une table individu, chaque individu a (en principe un père (CLE_PERE) et une mere (CLE_MERE)) donc en partant de moi CAZAUX Philippe (ou d un autre individu) je voudrais avoir les individus par ordre de generation avec aussi une colonne qui donne la génération

    Merci de votre aide

    Quand ils auront coupé le dernier arbre, pollué le dernier ruisseau, pêché le dernier poisson. Alors ils verront que l'argent ne se mange pas
    Citation de Sitting Bull - Chef des Indiens Sioux
    _______________________________
     MacBookPro 13" TouchBar - OS X : 10.16 Big Sur
     Iphone XR - IOS 14

    https://www.youtube.com/channel/UCMJ...IsRN_OA/videos

  2. #2
    Nouveau membre du Club
    J ai bien trouvé ceci https://www.sqlite.org/lang_with.html

    mais je n y comprends
    Quand ils auront coupé le dernier arbre, pollué le dernier ruisseau, pêché le dernier poisson. Alors ils verront que l'argent ne se mange pas
    Citation de Sitting Bull - Chef des Indiens Sioux
    _______________________________
     MacBookPro 13" TouchBar - OS X : 10.16 Big Sur
     Iphone XR - IOS 14

    https://www.youtube.com/channel/UCMJ...IsRN_OA/videos

  3. #3
    Membre confirmé
    Bonjour,

    Le principe d'une requête récursive se base sur une clause WITH avec un UNION ALL:

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    WITH v(liste_colonnes)
    AS (select liste_colonnes
          from t
          where --> point de départ
          union all
          select liste_colonnes
          from v
          join ... on
          where --> condition d'arrêt
         )

    Le "haut" du bloc représente le point de départ. Ici par exemple ce serait cle_fiche=2, mais tu peux très bien avoir plusieurs points de départ, si tu veux par exemple la généalogie de plusieurs personnes.
    Le "bas" du bloc représente la récursivité, c'est elle qui permet de boucler, et c'est pour ça que tu retrouves le nom de la vue with déclarée au début. En gros tu vas joindre "v" sur individu sur chaque cle_pere et cle_mere. Tu t'arrêtes quand ces derniers son null.

    Pourrais-tu fournir un jeu de données (par exemple les inserts) pour que l'on puisse tester?

    Merci

  4. #4
    Nouveau membre du Club
    Vanagreg merci

    ai essayé d adapter, ai pas réussi

    voici script de ma petite base

    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
    BEGIN TRANSACTION;
    CREATE TABLE IF NOT EXISTS "INDIVIDU" (
    	"CLE_FICHE"	INTEGER NOT NULL UNIQUE,
    	"KLE_DOSSIER"	Integer,
    	"CLE_PERE"	Integer,
    	"CLE_MERE"	Integer,
    	"PREFIXE"	Varchar(30),
    	"NOM"	Varchar(40) NOT NULL,
    	"PRENOM"	Varchar(60),
    	"SURNOM"	Varchar(120),
    	"SUFFIXE"	Varchar(30),
    	"SEXE"	Integer,
    	"DATE_NAISSANCE"	Date,
    	"ANNEE_NAISSANCE"	Integer,
    	"DATE_DECES"	Date,
    	"ANNEE_DECES"	Integer,
    	"AGE_AU_DECES"	Integer,
    	"SOURCE"	Blob,
    	"COMMENT"	Blob,
    	"NUM_SOSA"	Double precision,
    	"NCHI"	Smallint,
    	"NMR"	Smallint,
    	"IND_CONFIDENTIEL"	Smallint,
    	"DATE_CREATION"	Timestamp,
    	"CREE_PAR_QUI"	Varchar(30),
    	"DATE_MODIF"	Timestamp,
    	"MODIF_PAR_QUI"	Varchar(30),
    	"INDI_PHOTO"	Blob,
    	PRIMARY KEY("CLE_FICHE" AUTOINCREMENT)
    );
    INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (1,NULL,3,4,NULL,'CAZAUX','Michel',NULL,NULL,NULL,'15/08/1947',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (2,NULL,3,4,NULL,'CAZAUX','Philippe',NULL,NULL,NULL,'02/08/1949',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'');
    INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (3,NULL,7,NULL,NULL,'CAZAUX','Gerard',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (4,NULL,9,10,NULL,'PUIBARAUD','Monique',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (5,NULL,NULL,4,NULL,'LOOS','SYLVIE',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (6,NULL,2,8,NULL,'CAZAUX','Kevin',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (7,NULL,NULL,NULL,NULL,'CAZAUX','Alexandre',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (8,NULL,NULL,NULL,NULL,'JACQUES','Francoise',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (9,NULL,11,12,NULL,'PUIBARAUD','Edgard',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (10,NULL,NULL,NULL,NULL,'ARTHAPIGNET','Marie',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (11,NULL,NULL,NULL,NULL,'PUIBARAUD','Hipolytte',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (12,NULL,NULL,NULL,NULL,'ABADIE','Julia',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
    CREATE INDEX IF NOT EXISTS "NOM_INDIVIDU" ON "INDIVIDU" (
    	"NOM"
    );
    CREATE VIEW LISTE_INDI_PARENTS AS
    select 
           t.nom as nom_individu,
           t.prenom as prenom_individu,
    	   t.date_naissance as ne_individu,
           t_pere.nom as nom_pere,
           t_pere.prenom as prenom_pere,
           t_mere.nom as nom_mere,
           t_mere.prenom as prenom_mere
    from INDIVIDU t
    left join INDIVIDU t_pere on t_pere.cle_fiche = t.cle_pere
    left join INDIVIDU t_mere on t_mere.cle_fiche = t.cle_mere;
    COMMIT;
    Quand ils auront coupé le dernier arbre, pollué le dernier ruisseau, pêché le dernier poisson. Alors ils verront que l'argent ne se mange pas
    Citation de Sitting Bull - Chef des Indiens Sioux
    _______________________________
     MacBookPro 13" TouchBar - OS X : 10.16 Big Sur
     Iphone XR - IOS 14

    https://www.youtube.com/channel/UCMJ...IsRN_OA/videos

  5. #5
    Nouveau membre du Club
    sur la table pas sur la vue qui est pour autre chose
    Quand ils auront coupé le dernier arbre, pollué le dernier ruisseau, pêché le dernier poisson. Alors ils verront que l'argent ne se mange pas
    Citation de Sitting Bull - Chef des Indiens Sioux
    _______________________________
     MacBookPro 13" TouchBar - OS X : 10.16 Big Sur
     Iphone XR - IOS 14

    https://www.youtube.com/channel/UCMJ...IsRN_OA/videos

  6. #6
    Rédacteur

    Voir l'article que j'ai écrit à ce sujet : https://sqlpro.developpez.com/cours/...te-recursives/

    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  7. #7
    Nouveau membre du Club
    J ai essayé d adapter la requête de Fréderic

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    WITH tree (data, id, level, pathstr)                        
    AS (SELECT VHC_NAME, VHC_ID, 0,
               CAST('' AS VARCHAR(MAX))           
        FROM   T_VEHICULE                       
        WHERE  VHC_ID_FATHER IS NULL          
        UNION ALL                               
        SELECT VHC_NAME, VHC_ID, t.level + 1, t.pathstr + V.VHC_NAME
        FROM   T_VEHICULE V                     
               INNER JOIN tree t 
                     ON t.id = V.VHC_ID_FATHER)
    SELECT SPACE(level) + data as data, id, level, pathstr
    FROM   tree
    ORDER  BY pathstr, id


    Pas réussi
    Quand ils auront coupé le dernier arbre, pollué le dernier ruisseau, pêché le dernier poisson. Alors ils verront que l'argent ne se mange pas
    Citation de Sitting Bull - Chef des Indiens Sioux
    _______________________________
     MacBookPro 13" TouchBar - OS X : 10.16 Big Sur
     Iphone XR - IOS 14

    https://www.youtube.com/channel/UCMJ...IsRN_OA/videos

  8. #8
    Membre confirmé
    La difficulté ici c'est qu'il y a en fait 2 clauses récursives à faire, l'une pour les ascendants masculins, l'autre pour les ascendants féminins. Voici donc la requête en commençant par exemple par cle_fiche = 2:

    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
    with v1(cle_fiche, nom, prenom, cle_pere, cle_mere, nom_ascendant_masculin, prenom_ascendant_masculin, nom_ascendant_feminin, prenom_ascendant_feminin, niveau) 
    as (select i.cle_fiche, i.nom, i.prenom, pere.cle_fiche, mere.cle_fiche, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 
        from individu i
        join individu pere on pere.cle_fiche = i.cle_pere 
        join individu mere on mere.cle_fiche = i.cle_mere 
        where i.cle_fiche = 2
        union all 
        select pere.cle_fiche, pere.nom, pere.prenom, pere.cle_pere, mere.cle_mere, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 + niveau 
        from v1
        join individu pere on pere.cle_fiche = v1.cle_pere 
        left join individu mere on mere.cle_fiche = pere.cle_mere
        ) ,
    v2(cle_fiche, nom, prenom, cle_pere, cle_mere, nom_ascendant_masculin, prenom_ascendant_masculin, nom_ascendant_feminin, prenom_ascendant_feminin, niveau) 
    as (select i.cle_fiche, i.nom, i.prenom, pere.cle_fiche, mere.cle_fiche, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 
        from individu i
        join individu pere on pere.cle_fiche = i.cle_pere 
        join individu mere on mere.cle_fiche = i.cle_mere 
        where i.cle_fiche = 2
        union all 
        select mere.cle_fiche, mere.nom, mere.prenom, pere.cle_pere, mere.cle_mere, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 + niveau 
        from v2 
        join individu mere on mere.cle_fiche = v2.cle_mere
        left join individu pere on pere.cle_fiche = V2.cle_pere
        )     
    select case when v1.nom is null then v2.nom else v1.nom end nom, 
           case when v1.prenom is null then v2.prenom else v1.prenom end prenom, 
           case when v1.niveau > 1 then lead(v1.nom_ascendant_masculin) over (order by v1.niveau) else v1.nom_ascendant_masculin end nom_ascendant_masculin,
           case when v1.niveau > 1 then lead(v1.prenom_ascendant_masculin) over (order by v1.niveau) else v1.prenom_ascendant_masculin end prenom_ascendant_masculin,
           case when v2.niveau > 1 then lead(v2.nom_ascendant_feminin) over (order by v2.niveau) else v2.nom_ascendant_feminin end nom_ascendant_feminin,
           case when v2.niveau > 1 then lead(v2.prenom_ascendant_feminin) over (order by v2.niveau) else v2.prenom_ascendant_feminin end prenom_ascendant_feminin,
           case when v1.niveau is null then v2.niveau else v1.niveau end niveau
    from v1
    full join v2 on v2.cle_fiche = v1.cle_fiche
    order by niveau;



  9. #9
    Nouveau membre du Club
    Vanagreg, merci bcp pour ton travail mais chez moi ca ne passe pas

    L'exécution s'est terminée avec des erreurs.
    Résultat : RIGHT and FULL OUTER JOINs are not currently supported
    Quand ils auront coupé le dernier arbre, pollué le dernier ruisseau, pêché le dernier poisson. Alors ils verront que l'argent ne se mange pas
    Citation de Sitting Bull - Chef des Indiens Sioux
    _______________________________
     MacBookPro 13" TouchBar - OS X : 10.16 Big Sur
     Iphone XR - IOS 14

    https://www.youtube.com/channel/UCMJ...IsRN_OA/videos

  10. #10
    Nouveau membre du Club
    ai essayé de comprendre, en fait c est le mot full devant le join de la fin qui est refusé, si je l enlevé et laisse juste join la requête est accepté mais je n ai plus qu un seul enregistrement si je mets LEFT JOIN la ai 3 enregistrements

    FULL n est pas accepté : https://www.sqlitetutorial.net/sqlit...ll-outer-join/
    Quand ils auront coupé le dernier arbre, pollué le dernier ruisseau, pêché le dernier poisson. Alors ils verront que l'argent ne se mange pas
    Citation de Sitting Bull - Chef des Indiens Sioux
    _______________________________
     MacBookPro 13" TouchBar - OS X : 10.16 Big Sur
     Iphone XR - IOS 14

    https://www.youtube.com/channel/UCMJ...IsRN_OA/videos

  11. #11
    Membre confirmé
    Bonjour,

    Ok le full outer join n'est pas supporté par SQLlite. Il faut l'émluler avec des left join et un union all, comme 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
    19
    20
    21
    22
    23
    24
    25
    26
    v1_full_join_v2
    as (select 1 ascendant,
               v1.cle_fiche,
               v1.nom,
               v1.prenom,
               v1.nom_ascendant_masculin,
               v1.prenom_ascendant_masculin,
               v2.nom_ascendant_feminin,
               v2.prenom_ascendant_feminin,
               v1.niveau
        from v1
        left join v2 on v1.cle_fiche = v2.cle_fiche
        union all
        select 2 ascendant,
               v2.cle_fiche,
               v2.nom,
               v2.prenom,
               v1.nom_ascendant_masculin,
               v1.prenom_ascendant_masculin,
               v2.nom_ascendant_feminin,
               v2.prenom_ascendant_feminin,
               v2.niveau
        from v2
        left join v1 on v1.cle_fiche = v2.cle_fiche
        where v1.cle_fiche is null
       )


    ce qui donne pour requête globale:

    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
    with v1(cle_fiche, nom, prenom, cle_pere, cle_mere, nom_ascendant_masculin, prenom_ascendant_masculin, nom_ascendant_feminin, prenom_ascendant_feminin, niveau) 
    as (select i.cle_fiche, i.nom, i.prenom, pere.cle_fiche, mere.cle_fiche, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 
        from individu i
        join individu pere on pere.cle_fiche = i.cle_pere 
        join individu mere on mere.cle_fiche = i.cle_mere 
        where i.cle_fiche = 2
        union all 
        select pere.cle_fiche, pere.nom, pere.prenom, pere.cle_pere, mere.cle_mere, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 + niveau 
        from v1
        join individu pere on pere.cle_fiche = v1.cle_pere 
        left join individu mere on mere.cle_fiche = pere.cle_mere
        ) ,
    v2(cle_fiche, nom, prenom, cle_pere, cle_mere, nom_ascendant_masculin, prenom_ascendant_masculin, nom_ascendant_feminin, prenom_ascendant_feminin, niveau) 
    as (select i.cle_fiche, i.nom, i.prenom, pere.cle_fiche, mere.cle_fiche, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 
        from individu i
        join individu pere on pere.cle_fiche = i.cle_pere 
        join individu mere on mere.cle_fiche = i.cle_mere 
        where i.cle_fiche = 2
        union all 
        select mere.cle_fiche, mere.nom, mere.prenom, pere.cle_pere, mere.cle_mere, pere.nom, pere.prenom, mere.nom, mere.prenom, 1 + niveau 
        from v2 
        join individu mere on mere.cle_fiche = v2.cle_mere
        left join individu pere on pere.cle_fiche = V2.cle_pere
        ),
    v1_full_join_v2
    as (select 1 ascendant,
               v1.cle_fiche,
               v1.nom,
               v1.prenom,
               v1.nom_ascendant_masculin,
               v1.prenom_ascendant_masculin,
               v2.nom_ascendant_feminin,
               v2.prenom_ascendant_feminin,
               v1.niveau
        from v1
        left join v2 on v1.cle_fiche = v2.cle_fiche
        union all
        select 2 ascendant,
               v2.cle_fiche,
               v2.nom,
               v2.prenom,
               v1.nom_ascendant_masculin,
               v1.prenom_ascendant_masculin,
               v2.nom_ascendant_feminin,
               v2.prenom_ascendant_feminin,
               v2.niveau
        from v2
        left join v1 on v1.cle_fiche = v2.cle_fiche
        where v1.cle_fiche is null
       )    
     
    select nom, 
           prenom, 
           case when niveau > 1 then lead(nom_ascendant_masculin) over (partition by ascendant order by niveau) else nom_ascendant_masculin end nom_ascendant_masculin,
           case when niveau > 1 then lead(prenom_ascendant_masculin) over (partition by ascendant order by niveau) else prenom_ascendant_masculin end prenom_ascendant_masculin,
           case when niveau > 1 then lead(nom_ascendant_feminin) over (partition by ascendant order by niveau) else nom_ascendant_feminin end nom_ascendant_feminin,
           case when niveau > 1 then lead(prenom_ascendant_feminin) over (partition by ascendant order by niveau) else prenom_ascendant_feminin end prenom_ascendant_feminin,
           niveau
    from v1_full_join_v2
    order by niveau;

  12. #12
    Nouveau membre du Club
    Merci encore cette fois ci elle est acceptée mais le résultat est incomplet je devrais avoir


    [TH]Niveau[/TH]
    [TH]Nom[/TH]
    1 CAZAUX Philippe
    2 CAZAUX Gerad
    2 PUIBARAUD Monique
    3 CAZAUX Alexandre
    3 PUIBARAUD Edgard
    3 ARTHAPIGNET Marie
    4 PUIBARAUD Hippolyte
    4 ABADIE Juila

    merci à toi
    Quand ils auront coupé le dernier arbre, pollué le dernier ruisseau, pêché le dernier poisson. Alors ils verront que l'argent ne se mange pas
    Citation de Sitting Bull - Chef des Indiens Sioux
    _______________________________
     MacBookPro 13" TouchBar - OS X : 10.16 Big Sur
     Iphone XR - IOS 14

    https://www.youtube.com/channel/UCMJ...IsRN_OA/videos

  13. #13
    Membre confirmé
    Tu as raison. Je me suis rendu compte que j'avais compliqué inutilement la requête. En fait une seule requête récursive est nécessaire (j'ai changé la condition de jointure). Comme on ne peut pas accéder au parent directement, j'utilise la fonction analytique LEAD qui permet d'aller chercher un ligne par rapport à un offset. Enfin, pour avoir le père et la mère sur la même ligne, je pivote à l'aide d'un MAX(CASE WHEN...END). Voici la requête:
    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
     
    with v(cle_fiche, nom, prenom, genre, cle_fiche_parent, nom_parent, prenom_parent, cle_pere, cle_mere, niveau)
    as (select i.cle_fiche, i.nom, i.prenom, case when p.cle_fiche = i.cle_pere then 1 else 2 end genre, p.cle_fiche cle_fiche_parent, p.nom nom_parent, p.prenom prenom_parent, i.cle_pere, i.cle_mere, 1 
        from individu i
        join individu p on p.cle_fiche in (i.cle_pere, i.cle_mere) 
        where i.cle_fiche = 2
        union all
        select i.cle_fiche, i.nom, i.prenom, case when i.cle_fiche = v.cle_pere then 1 else 2 end, i.cle_fiche, i.nom, i.prenom, i.cle_pere, i.cle_mere, 1 + niveau 
        from v
        join individu i on i.cle_fiche in (v.cle_pere, v.cle_mere)
       ),   
    v_dedoublonnee 
    as (select distinct 
           nom, 
           prenom,
           genre,
           nom_parent,
           prenom_parent,
           niveau
        from v 
       ),
    v_parents 
    as (select nom, 
               prenom,
               genre,
               case when niveau > 1 then lead(nom_parent) over (partition by genre order by niveau) else nom_parent end nom_parent,
               case when niveau > 1 then lead(prenom_parent) over (partition by genre order by niveau) else prenom_parent end prenom_parent,
               niveau
        from v_dedoublonnee
       )
    select niveau,
           nom,
           prenom,
           max(case when genre = 1 then nom_parent end) nom_pere,
           max(case when genre = 1 then prenom_parent end) prenom_pere,
           max(case when genre = 2 then nom_parent end) nom_mere,
           max(case when genre = 2 then prenom_parent end) prenom_mere
    from  v_parents
    group by niveau,
             nom,
             prenom
    order by niveau;



###raw>template_hook.ano_emploi###