Discussion :

[PHCM64310]

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

[PHCM64310]

J ai bien trouvé ceci https://www.sqlite.org/lang_with.html

mais je n y comprends

[vanagreg]

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

[PHCM64310]

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;

[PHCM64310]

sur la table pas sur la vue qui est pour autre chose

[SQLpro]

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

A +