Roy'sWiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== SQL =====

==== code intersection de deux arbre et aucun doublon: ====
<code SQL anc.sql>
/*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;
</code>

==== code recursion et location ====

<code SQL locate.sql>
with recursive anc1 as(select * from t_person where id=1673247 UNION 
select p.* from t_person p join anc1 on anc1.idf=p.id or anc1.idm=p.id),
anc2 as(select * from t_person where id=1673246 UNION 
select p.* from t_person p join anc2 on anc2.idf=p.id or anc2.idm=p.id) 
select max(py) py,max(pr) pr,lat,lon,group_concat(u.id,': ',u.Nom,' | ',u.Prenom SEPARATOR '\n') p from 
(select anc1.* from anc1 join anc2 using(id)) as u 
join plr using(id) 
join places on n_lid=lid or d_lid=lid 
where lat is not null group by lat,lon
</code>

==== code recurtion et recherche de parent simple inconue ====
<code SQL simple.sql>
with recursive anc as(select * from t_person where id=9999999 UNION
select p.* from t_person p join anc on p.id=anc.idm or p.id=anc.idf)
select * from anc where idm is NULL xor idf is NULL
</code>
Roy'sWiki

User Tools

Site Tools

Page Tools
