p-v3 - Quarry

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

use frwiki_p;
select #concat('[[:de:',p.page_title,'|',p.page_title,']]') as dewiki, concat('[[',p.page_title,']]') as enwiki, count(l.ll_lang) as 'number of iw'
concat('* ',p.page_title,' - ',count(l.ll_lang)),

(select group_concat(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 like "Naissance_en%"
                                             ) as kat

#p.page_title, count(l.ll_lang)
from langlinks l
join page p on p.page_id=l.ll_from
where  l.ll_from in (select tl.tl_from from templatelinks tl
						where tl.tl_title="Infobox_Cinéma_(personnalité)" and tl_namespace=10 and tl.tl_from_namespace=0)
and 
p.page_namespace=0 and l.ll_from not in (select m.ll_from from langlinks m where m.ll_lang="lv")
group by l.ll_from
#having count(l.ll_lang)>20
order by count(l.ll_lang) desc
limit 600;
/*
use ltgwiki_p;
select #concat('[[:de:',p.page_title,'|',p.page_title,']]') as dewiki, concat('[[',p.page_title,']]') as enwiki, count(l.ll_lang) as 'number of iw'
concat('* [[:ltg:',p.page_title,'|',p.page_title,']] - [[',p.page_title,']] - ',count(l.ll_lang))
#p.page_title, count(l.ll_lang)
from langlinks l
join page p on p.page_id=l.ll_from
where # l.ll_from in (select tl.tl_from from templatelinks tl
	#					where tl.tl_title="Infobox_Athlète" and tl_namespace=10 and tl.tl_from_namespace=0)
#and 
p.page_namespace=0 and l.ll_from not in (select m.ll_from from langlinks m where m.ll_lang="lv")
group by l.ll_from
#having count(l.ll_lang)>20
order by count(l.ll_lang) desc
limit 600;

use enwiki_p;
select l.ll_from, count(l.ll_lang)
from langlinks l
where  l.ll_from in (select p.page_id from pagelinks pl
						join page p on p.page_title=pl.pl_title and pl.pl_namespace=p.page_namespace
						where pl.pl_from=25639857 and pl.pl_namespace=0)
and l.ll_from not in (select m.ll_from from langlinks m where m.ll_lang="lv")
group by l.ll_from
having count(l.ll_lang)>20
order by count(l.ll_lang) desc

templatelinks

where  l.ll_from in (select tl.tl_from from templatelinks tl
						where tl.tl_title="Infobox_NRHP" and tl_namespace=10 and tl.tl_from_namespace=0)
                   
categorylinks
                   
where  l.ll_from in (select p.page_id from pagelinks pl
						join page p on p.page_title=pl.pl_title and pl.pl_namespace=p.page_namespace
						where pl.pl_from=21259008 and pl.pl_namespace=0)
                        
                
vnk pec iw
use enwiki_p;
select l.ll_from, count(l.ll_lang)
from langlinks l
join page p on p.page_id=l.ll_from
where l.ll_from not in (select m.ll_from from langlinks m where m.ll_lang="lv") and p.page_namespace=828
group by l.ll_from
#having count(l.ll_lang)>20
order by count(l.ll_lang) desc
limit 50;


standarts - SĀKUMVARIANTS
use enwiki_p;
select l.ll_from, count(l.ll_lang)
from langlinks l
where  l.ll_from in (select p.page_id from pagelinks pl
						join page p on p.page_title=pl.pl_title and pl.pl_namespace=p.page_namespace
						where pl.pl_from=21259008 and pl.pl_namespace=0)
and l.ll_from not in (select m.ll_from from langlinks m where m.ll_lang="lv")
group by l.ll_from
having count(l.ll_lang)>20
order by count(l.ll_lang) desc


de->en
use dewiki_p;
select p.page_title, l.ll_from, count(l.ll_lang)
from langlinks l
join page p on p.page_id=l.ll_from
where l.ll_from not in (select m.ll_from from langlinks m where m.ll_lang="en") and p.page_namespace=0
#and l.ll_from=1570903
group by l.ll_from
having count(l.ll_lang)>14
order by count(l.ll_lang) desc
limit 700;

unesco fr
use frwiki_p;
select p.page_title, count(l.ll_lang)
from langlinks l
join page p on l.ll_from=p.page_id
where  l.ll_from in (select tl.tl_from from templatelinks tl
						where tl.tl_title="Portail_patrimoine_mondial" and tl_namespace=10 and tl.tl_from_namespace=0)
and l.ll_from not in (select tl.tl_from from templatelinks tl
						where tl.tl_title="Infobox_Subdivision_administrative" and tl_namespace=10 and tl.tl_from_namespace=0)
and l.ll_from not in (select m.ll_from from langlinks m where m.ll_lang="lv")
group by l.ll_from
having count(l.ll_lang)>20
order by count(l.ll_lang) desc

use be_x_oldwiki_p;
select p.page_title, count(l.ll_lang)
from langlinks l
join page p on p.page_id=l.ll_from
where l.ll_from not in (select m.ll_from from langlinks m where m.ll_lang="ru") and p.page_namespace=0
and l.ll_from not in (select m.ll_from from langlinks m where m.ll_lang="en")
group by l.ll_from
#having count(l.ll_lang)>50
order by count(l.ll_lang) desc
limit 1000;
*/
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...
