Differences

This shows you the differences between two versions of the page.

--- vincent:sql [2019/05/03 21:58]
admin
+++ vincent:sql [2019/05/03 23:39] (current)
admin
@@ Line 1: / Line 1: @@
 ===== SQL =====
-ceci est un test de syntaxe
+==== code intersection de deux arbre et aucun doublon: ====
-<code SQL>
+<code SQL anc.sql>
--- select schema
+/*select schema*/
 use genealogy;
--- recursive cte1
+/*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
+/*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
+/*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
+/*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
+/*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

Differences

Page Tools