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

SQL

​x
 
USE lvwiki_p;
#SELECT p.page_title, COUNT(pl.pl_from) FROM page p
SELECT p.page_title
#   CONCAT('* [[Special:Whatlinkshere/',p.page_title,']]') as "Saites uz rakstu",
#   CONCAT(' - [[',p.page_title,']] - ') as "Raksts",
#   COUNT(pl.pl_from)
FROM page p
INNER JOIN categorylinks c ON p.page_id = c.cl_from AND c.cl_to = "Izolētie_raksti"
INNER JOIN pagelinks pl ON p.page_title = pl.pl_title AND p.page_namespace = pl.pl_namespace
INNER JOIN page p2 ON p2.page_id = pl.pl_from
AND pl.pl_from_namespace = 0 and p2.page_is_redirect=0# and p.page_title="Milica_Daboviča"
GROUP BY p.page_title
having COUNT(pl.pl_from)>0
ORDER BY COUNT(pl.pl_from) DESC
/* 
USE lvwiki_p;
SELECT disambigs.page_title, COUNT(DISTINCT links.pl_from) AS direct_links, COUNT(DISTINCT redirects.page_id) AS redirects,
    COUNT(DISTINCT links.pl_from) + COUNT(DISTINCT links_to_redirects.pl_from) AS all_backlinks
    FROM (SELECT * FROM categorylinks where cl_to = "Izolētie_raksti") AS disambigs
    JOIN page AS disambigs
        ON disambigs.page_id = cl_from
    JOIN (SELECT * FROM pagelinks WHERE pl_from_namespace = 0) AS links
        ON links.pl_title = disambigs.page_title AND links.pl_namespace = disambigs.page_namespace
    LEFT JOIN page AS redirects
        ON redirects.page_is_redirect = 1 AND redirects.page_id = links.pl_from AND redirects.page_namespace = disambigs.page_namespace
    LEFT JOIN (SELECT * FROM pagelinks WHERE pl_from_namespace = 0) AS links_to_redirects
        ON links_to_redirects.pl_title = redirects.page_title AND links_to_redirects.pl_namespace = redirects.page_namespace
GROUP BY disambigs.page_title
ORDER BY all_backlinks DESC, direct_links DESC, redirects, disambigs.page_title
LIMIT 100;
​
​
​
arī uz pāradresācijām
use lvwiki_p;
SELECT pl.pl_namespace, pl.pl_title, count(pl.pl_from), pl.pl_from_namespace#p.page_title, COUNT(pl.pl_from)
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 (SELECT p3.page_title
                    FROM page p3
                    INNER JOIN categorylinks c3 ON p3.page_id = c3.cl_from AND c3.cl_to = "Izolētie_raksti"
                    INNER JOIN pagelinks pl3 ON p3.page_title = pl3.pl_title AND p3.page_namespace = pl3.pl_namespace
                    INNER JOIN page p23 ON p23.page_id = pl3.pl_from
                    AND pl3.pl_from_namespace = 0)
                                                                                                         
                                                                                                         )
group by pl.pl_title
having count(pl.pl_from)>0
order by count(pl.pl_from)
​
​
USE lvwiki_p;
#SELECT p.page_title, COUNT(pl.pl_from) FROM page p
SELECT 
    CONCAT('* [[Special:Whatlinkshere/',p.page_title,']]') as "Saites uz rakstu",
    CONCAT(' - [[',p.page_title,']] - ') as "Raksts",
    COUNT(pl.pl_from)
FROM page p
INNER JOIN categorylinks c ON p.page_id = c.cl_from AND c.cl_to = "Izolētie_raksti"
INNER JOIN pagelinks pl ON p.page_title = pl.pl_title AND p.page_namespace = pl.pl_namespace
INNER JOIN page p2 ON p2.page_id = pl.pl_from
AND pl.pl_from_namespace = 0 and p2.page_is_redirect=0# and p.page_title="Milica_Daboviča"
GROUP BY p.page_title
having COUNT(pl.pl_from)>0
ORDER BY COUNT(pl.pl_from) DESC
limit 100;
​
​
​
USE lvwiki_p;
SELECT p.page_title, COUNT(pl.pl_from) FROM page p, pagelinks plw
INNER JOIN categorylinks c ON p.page_id = c.cl_from AND c.cl_to = "Izolētie_raksti"
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 plw.page_is_redirect=0
GROUP BY p.page_title
ORDER BY COUNT(pl.pl_from) DESC
​
use lvwiki_p;
SELECT
  page_namespace,
  page_title
FROM page
LEFT JOIN pagelinks
ON pl_title = page_title
AND pl_namespace = page_namespace
WHERE pl_namespace=0 and page_is_redirect=0
LIMIT 1;
*/
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
All SQL code is licensed under CC0 License.

Checking query status...