Untitled query #8586

This query is marked as a draft This query has been published by Edgars2007.

exists (select *
		from categorylinks cla
		#join page pla on cla.cl_from=pla.page_id# and cla.tl_from_
		where l.ll_from=cla.cl_from and
 cla.cl_type="page" and cla.cl_to in ("Portail:Hongrie/Articles_liés","Portail:Budapest/Articles_liés")
                                             )

use frwiki_p;
select concat('* ',count(l.ll_lang),' [[',p.page_title,']]')#, GROUP_CONCAT(distinct cla.cl_to separator ', ')
from langlinks l
inner join page p on p.page_id=l.ll_from
inner join categorylinks cla on cla.cl_type="page" and p.page_id=cla.cl_from
where cla.cl_to in ("Portail:Hongrie/Articles_liés","Portail:Budapest/Articles_liés")
                                             
                                             
and not exists (select * from templatelinks tl
						where l.ll_from=tl.tl_from and (tl.tl_title="Infobox_Personnalité_politique" or tl.tl_title="Infobox_Subdivision_administrative")
                				and tl.tl_namespace=10 and tl.tl_from_namespace=0)
and p.page_namespace=0 and not exists (select * from langlinks m where m.ll_from=l.ll_from and m.ll_lang="lv")
group by l.ll_from
#having count(l.ll_lang)>20
order by count(l.ll_lang) desc
limit 10;

use frwiki_p;
select concat('* ',count(l.ll_lang),' [[',p.page_title,']]'), GROUP_CONCAT(distinct cla.cl_to separator ', '), 
(select ll_title from langlinks m where m.ll_from=l.ll_from and m.ll_lang="lv"),
(select cla1.cl_to
		from categorylinks cla1
		#join page pla on cla.cl_from=pla.page_id# and cla.tl_from_
		where l.ll_from=cla1.cl_from and
 cla1.cl_type="page" and cla1.cl_to="Portail:Prix_Nobel/Articles_liés" limit 1
                                             ) as "Nobel", (select cla2.cl_to
		from categorylinks cla2
		#join page pla on cla.cl_from=pla.page_id# and cla.tl_from_
		where l.ll_from=cla2.cl_from and
 cla2.cl_type="page" and cla2.cl_to="Portail:Patrimoine_mondial/Articles_liés" limit 1
                                             ) as "UNESCO"

from langlinks l
join page p on p.page_id=l.ll_from
join categorylinks cla on cla.cl_type="page" and l.ll_from=cla.cl_from
where cla.cl_to in ("Portail:Hongrie/Articles_liés","Portail:Budapest/Articles_liés")
    #  and page_title="Parlement_hongrois"
    
and not exists (select * from templatelinks tl
						where l.ll_from=tl.tl_from and (tl.tl_title="Infobox_Personnalité_politique" or tl.tl_title="Infobox_Subdivision_administrative")
                				and tl.tl_namespace=10 and tl.tl_from_namespace=0)
and p.page_namespace=0 and not exists (select * from langlinks m where m.ll_from=l.ll_from and m.ll_lang="lv")
group by l.ll_from, cla.cl_to
#having count(l.ll_lang)>20
order by count(l.ll_lang) desc
limit 1000;


("Portail:Albanie/Articles_liés",
"Portail:Arménie/Articles_liés",
"Portail:Autriche/Articles_liés","Portail:Innsbruck/Articles_liés","Portail:Vienne_(Autriche)/Articles_liés",#"Portail:Empire autrichien/Articles_liés",
"Portail:Azerbaïdjan/Articles_liés",
"Portail:Bakou/Articles_liés",
"Portail:Biélorussie/Articles_liés",
#Bashkortostan- nav
"Portail:Bosnie-Herzégovine/Articles_liés",
"Portail:Bulgarie/Articles_liés",
"Portail:République_tchèque/Articles_liés",#vrb atsevišķi
"Portail:Tchécoslovaquie/Articles_liés","Portail:Prague/Articles_liés",
#esperanto
"Portail:Géorgie_(pays)/Articles_liés",
#grieķija
"Portail:Croatie/Articles_liés",
#igaunija, krievija, lietuva
"Portail:Macédoine/Articles_liés","Portail:Skopje/Articles_liés",
"Portail:Moldavie/Articles_liés",
#polija
"Portail:Roumanie/Articles_liés","Portail:Bucarest/Articles_liés",
#Republika Srpska
"Portail:Serbie/Articles_liés","Portail:Belgrade/Articles_liés",
"Portail:Slovaquie/Articles_liés","Portail:Bratislava/Articles_liés","Portail:Košice/Articles_liés",
#ukraina
"Portail:Hongrie/Articles_liés","Portail:Budapest/Articles_liés",
                   #########
"Portail:Grèce/Articles_liés","Portail:Athènes/Articles_liés","Portail:Crète/Articles_liés",
"Portail:Kazakhstan/Articles_liés",
"Portail:Chypre/Articles_liés",
"Portail:Kosovo/Articles_liés",
"Portail:Monténégro/Articles_liés",
"Portail:Slovénie/Articles_liés",
"Portail:Turquie/Articles_liés","Portail:Istanbul/Articles_liés")

SQL

use frwiki_p;
select distinct concat('* ',count(l.ll_lang),' [[',p.page_title,']]'), GROUP_CONCAT(distinct cla.cl_to separator ', ')
from langlinks l
inner join page p on p.page_id=l.ll_from
inner join categorylinks cla on cla.cl_type="page" and l.ll_from=cla.cl_from
where cla.cl_to in ("Portail:Hongrie/Articles_liés","Portail:Budapest/Articles_liés")
    #  and page_title="Parlement_hongrois"                                       
                                             
and not exists (select * from templatelinks tl
						where l.ll_from=tl.tl_from and (tl.tl_title="Infobox_Personnalité_politique" or tl.tl_title="Infobox_Subdivision_administrative")
                				and tl.tl_namespace=10 and tl.tl_from_namespace=0)
and p.page_namespace=0 and not exists (select * from langlinks m where m.ll_from=l.ll_from and m.ll_lang="lv")
group by l.ll_from#, cla.cl_to
#having count(l.ll_lang)>20
order by count(l.ll_lang) desc
limit 10;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...