قائمة المقالات اليتيمة التي يمكن إصلاحها بإنشاء مقالات خريجي الجامعات

SQL

use enwiki_p;
select p3.page_title, (select count(*) from arwiki_p.pagelinks where arwiki_p.pagelinks.pl_title = p3.page_title
                 and arwiki_p.pagelinks.pl_from_namespace = 0
                 and arwiki_p.pagelinks.pl_namespace = 0) as "pp", count(*) as "pp1"
from page p1
inner join categorylinks
on cl_from = p1.page_id
inner join pagelinks
on pl_from = p1.page_id
inner join page p2
on p2.page_title = pl_title
inner join langlinks
on ll_from = p2.page_id
inner join arwiki_p.page p3
on p3.page_title = replace(ll_title," ","_")
where cl_to like "Lists_of_people_by_university_or_college%"
and p1.page_namespace = 0
and p1.page_is_redirect = 0
and pl_from_namespace = 0
and pl_namespace = 0
and p2.page_namespace = 0
and p2.page_is_redirect = 0
and p3.page_namespace = 0
and p3.page_is_redirect = 0
and ll_lang = "ar"
and p3.page_id in (select arwiki_p.categorylinks.cl_from from arwiki_p.categorylinks 
                   where arwiki_p.categorylinks.cl_from = p3.page_id and arwiki_p.categorylinks.cl_to = "جميع_المقالات_اليتيمة")
group by pl_title
order by count(*) desc
having (pp1 + pp) > 3
limit 10;

SQL

Checking query status...