Jump to content

User:Gmaxwell/math fu2

From Wikipedia, the free encyclopedia

mysql> select count(distinct mp.page_title) from page as lp join pagelinks on pl_from=lp.page_id join page as mp on (mp.page_title=pl_title and mp.page_namespace=0) where lp.page_namespace=0 and pl_namespace=0 and lp.page_title like 'List_of_mathematics_articles%' and not exists (select 1 from categorylinks where cl_from=mp.page_id and (cl_to like '%book%' or cl_to like '19%births' or cl_to like '18%births' or cl_to like '%people%' or cl_to like '%films' or cl_to like '%journals' or cl_to like 'Novels%' or cl_to like '%novels' or cl_to like '%literature%' or cl_to like '%institutes' or cl_to like '%characters' or cl_to like 'Television_show%' or cl_to like '%television series' or cl_to like '%software' or cl_to like '%websites' or cl_to like '%plays' or cl_to like '%songs' or cl_to like '%albums' or cl_to like 'Television_series%' or cl_to like '19%paintings' or cl_to like '%societies') limit 1);
+-------------------------------+
| count(distinct mp.page_title) |
+-------------------------------+
|                         13452 | 
+-------------------------------+
1 row in set (2 min 53.58 sec)

mysql> select concat('*[[',mp.page_title,']] - ',ip.page_title) from page as lp join pagelinks on pl_from=lp.page_id join page as mp on (mp.page_title=pl_title and mp.page_namespace=0) join imagelinks on il_from=mp.page_id join page as ip on ip.page_title=il_to and ip.page_namespace=6 where lp.page_namespace=0 and pl_namespace=0 and lp.page_title like 'List_of_mathematics_articles%' and exists (select 1 from templatelinks where tl_from=ip.page_id and tl_namespace=10 and tl_title like 'Non-free%') and ip.page_title!='Commons-logo.svg' and not exists (select 1 from categorylinks where cl_from=mp.page_id and (cl_to like '%book%' or cl_to like '19%births' or cl_to like '18%births' or cl_to like '%people%' or cl_to like '%films' or cl_to like '%journals' or cl_to like 'Novels%' or cl_to like '%novels' or cl_to like '%literature%' or cl_to like '%institutes' or cl_to like '%characters' or cl_to like 'Television_show%' or cl_to like '%television series' or cl_to like '%software' or cl_to like '%websites' or cl_to like '%plays' or cl_to like '%songs' or cl_to like '%albums' or cl_to like 'Television_series%' or cl_to like '19%paintings' or cl_to like '%societies') limit 1) group by mp.page_title, ip.page_title order by mp.page_title, ip.page_title;
+-------------------------------------------------------------------------------------+
| concat('*[[',mp.page_title,']] - ',ip.page_title)                                   |
+-------------------------------------------------------------------------------------+



for further review

[edit]