Dalībnieks:Edgars2007/SQL

Vikipēdijas lapa
pl_from || pl_namespace || pl_title                    || pl_from_namespace
94069	   0	           Limbažu_viļņotais_līdzenums	  10

Citi potenciāli noderīgi[labot šo sadaļu | labot pirmkodu]

--- varbūt atdalīt, cik linku ir uz lapām

Vaicājumi, ko nedrīkst atļauties pazaudēt[labot šo sadaļu | labot pirmkodu]

quarry:query/2936

USE lvwiki_p;
SELECT p.page_title, COUNT(t.tl_title) FROM page p
INNER JOIN categorylinks c ON p.page_id = c.cl_from AND c.cl_to = '1800_births'
LEFT JOIN templatelinks t ON p.page_id = t.tl_from AND t.tl_namespace = 10 AND t.tl_title IN ('Reflist', 'Denmark-writer-stub')
GROUP BY p.page_title
ORDER BY COUNT(t.tl_title) DESC
LIMIT 5

Disambigi[labot šo sadaļu | labot pirmkodu]

Oriģināls

USE lvwiki_p;
SELECT p.page_namespace, p.page_title, COUNT(pl.pl_from) FROM page p
INNER JOIN categorylinks c ON p.page_id = c.cl_from AND c.cl_to = 'Nozīmju_atdalīšana'
# INNER skips pages whose link count is zero. Change INNER to LEFT on the line below if you also want to include those:
INNER JOIN pagelinks pl ON p.page_title = pl.pl_title AND p.page_namespace = pl.pl_namespace
# This counts all links, including links from talk pages to disambiguation pages. If you only want to count
# links from the article namespace, then remove the # from the line below:
# AND pl.pl_from_namespace = 0
GROUP BY p.page_title
ORDER BY COUNT(pl.pl_from) DESC

USE lvwiki_p;
SELECT p.page_title, COUNT(pl.pl_from) FROM page p
INNER JOIN categorylinks c ON p.page_id = c.cl_from AND c.cl_to = 'Nozīmju_atdalīšana'
INNER JOIN pagelinks pl ON p.page_title = pl.pl_title AND p.page_namespace = pl.pl_namespace
AND pl.pl_from_namespace = 0 and p.page_namespace=0
GROUP BY p.page_title
ORDER BY COUNT(pl.pl_from) DESC

Saites uz pāradresācijām[labot šo sadaļu | labot pirmkodu]

SELECT pl.pl_namespace, pl.pl_title, count(pl.pl_from), pl.pl_from_namespace
from pagelinks pl
where pl.pl_namespace=0 
and pl.pl_from_namespace=0
and /*pl.pl_from_namespace=0 and  - svarīgi arī veidnēs izvākt*/ pl.pl_title in (SELECT p2.page_title
from pagelinks pl2
join page p2 on pl2.pl_from=p2.page_id
where p2.page_is_redirect=1 and pl2.pl_namespace=0
		and pl2.pl_from_namespace=0
      and /*pl2.pl_from_namespace=0 and  - svarīgi arī veidnēs izvākt*/pl2.pl_title in (...)
                                                                                                         
                                                                                                         )
group by pl.pl_title
order by count(pl.pl_from)