Differences

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

--- vincent:sql [2019/05/03 22:06]
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 anc.sql>
 /*select schema*/
@@ Line 22: / Line 22: @@
 /*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