Roy'sWiki

ceci est un test de syntaxe

/*select schema*/
USE genealogy;
 
/*recursive cte1*/
WITH recursive anc1 AS(SELECT *,0000001 st FROM t_person WHERE id=1673247 UNION 
SELECT p.*,((2*anc1.st)+(p.Sexe='F')) st FROM t_person p JOIN anc1 ON anc1.idf=p.id OR anc1.idm=p.id),
 
/*recursive cte2*/
anc2 AS(SELECT *,0000001 st FROM t_person WHERE id=1673246 UNION 
SELECT p.*,((2*anc2.st)+(p.Sexe='F')) st FROM t_person p JOIN anc2 ON anc2.idf=p.id OR anc2.idm=p.id),
 
/*find intersection between cte1 and cte2*/
ancm AS(SELECT anc1.*,least(anc1.st,anc2.st) lst FROM anc1 JOIN anc2 USING(id)),
 
/*colapse double entries with smallest stradonits*/
ancf AS(SELECT id,idm,idf,Prenom,Nom,Sexe,MIN(lst) mst FROM ancm GROUP BY id,idm,idf,Prenom,Nom,Sexe)
 
/*acutual selection of result*/
SELECT *,strad(mst) strad FROM ancf;