قائمة البوابات حسب عدد المقالات

SQL

SELECT main.page_title, COUNT(sub.page_id) as sub_page_count, COUNT(linked.pl_from) as linked_page_count
FROM page AS p
INNER JOIN (
    SELECT page_id, page_title
    FROM page 
    WHERE page_namespace = 100 
    AND page_is_redirect = 0 
    AND page_title NOT LIKE '%/%'
) AS main ON main.page_id = p.page_id
LEFT JOIN page AS sub ON main.page_title = SUBSTRING_INDEX(sub.page_title, '/', 1) AND sub.page_namespace = 100
LEFT JOIN pagelinks AS linked ON linked.pl_title = main.page_title AND linked.pl_namespace = 0
WHERE p.page_namespace = 0
GROUP BY main.page_title;

SQL

Checking query status...