SQL
x
USE trwiki_p;
SET @KAT1 = replace("Türkçe Vikipedi üzerindeki resmi Vikiveriden farklı olan maddeler"," ","_"); -- "Local image different than Wikidata"
SET @KAT2 = replace("Türkçe Vikipedi ile Vikiveride aynı resim olan maddeler"," ","_"); -- "Local image same as Wikidataa"
SET @zero = "0"; -- write "0" to edit section zero (introduction)
SET @userexculded = 104002; -- me, exclude pages last edited by me
SELECT concat("[[",p2.page_title,"]]") as sayfa
, concat("http://tr.wikipedia.org/wiki?action=edit§ion=",@zero,"&curid=",cl.cl_from,"") as url
, il.il_to as dosya, cl2.cl_to as kat, count(*)
, img_user_text, img_minor_mime
, rev_user, rev_user_text
from categorylinks cl join imagelinks il on il.il_from = cl.cl_from
join image on img_name = il.il_to
join page p on p.page_title = il.il_to join categorylinks cl2 on cl2.cl_from = p.page_id
join page p2 on p2.page_id = cl.cl_from
join revision_userindex rev on rev.rev_id = p2.page_latest
where p.page_namespace = 6
and (cl.cl_to = @KAT1 OR cl.cl_to = @KAT1)
and cl2.cl_to rlike "adil" -- fair use
and img_major_mime rlike "image"
and p.page_id not in (select cl999.cl_from from categorylinks cl999 where cl999.cl_to rlike "TIME")
and rev_user != @user_excluded
group by cl.cl_from
order by p2.page_latest
-- limit 100
;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.