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

SQL

​x
 
USE enwiki_p;
​
SELECT page_id,
       links.numlinks + IFNULL(redirlinks.numlinks, 0) - IFNULL(redirs.numredirs, 0) AS numlinks
FROM
(SELECT p.page_id AS page_id,
        count(*) AS numlinks
     FROM page p
     JOIN pagelinks pl
     ON (p.page_namespace=pl.pl_namespace
         AND p.page_title=pl.pl_title)
     WHERE p.page_id IN (30474802, 2557186, 22939867, 20712038, 22678539, 21040259, 40832620,
                         21081377, 28774397, 20779111, 8524927, 21894306, 21894387, 22877614, 20672772,
                         23665522, 25239240, 22851691, 18883277, 23733207, 28452168, 18818832,
                         49183967,20851187, 10824283)
     AND pl.pl_from_namespace=0
     GROUP BY p.page_id
) AS links
LEFT JOIN
(SELECT p1.page_id,
    count(*) AS numredirs
 FROM page p1
 JOIN redirect 
 ON (p1.page_namespace=rd_namespace
     AND page_title=rd_title)
 JOIN page p2
 ON rd_from=p2.page_id
 WHERE p2.page_namespace=0
 AND p1.page_id IN (30474802, 2557186, 22939867, 20712038, 22678539, 21040259, 40832620,
                         21081377, 28774397, 20779111, 8524927, 21894306, 21894387, 22877614, 20672772,
                         23665522, 25239240, 22851691, 18883277, 23733207, 28452168, 18818832,
                         49183967,20851187, 10824283)
 GROUP BY page_id
) AS redirs
USING (page_id)
LEFT JOIN
(SELECT p1.page_id,
    count(*) AS numlinks
 FROM page p1
 JOIN redirect 
 ON (p1.page_namespace=rd_namespace
     AND page_title=rd_title)
 JOIN page p2
 ON rd_from=p2.page_id
 JOIN pagelinks pl
 ON (p2.page_namespace=pl.pl_namespace
     AND p2.page_title=pl.pl_title)
 WHERE p2.page_namespace=0
 AND pl.pl_from_namespace=0
 AND p1.page_id IN (30474802, 2557186, 22939867, 20712038, 22678539, 21040259, 40832620,
                         21081377, 28774397, 20779111, 8524927, 21894306, 21894387, 22877614, 20672772,
                         23665522, 25239240, 22851691, 18883277, 23733207, 28452168, 18818832,
                         49183967,20851187, 10824283)
 GROUP BY page_id
) AS redirlinks
USING (page_id);
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...