USE trwiki_p;

SET @gun = date_add(now(), INTERVAL -30 DAY); -- gun sayisi buradan ayarlanabilir

SET @s1 = "[gG]erekçe";  -- konu1 

SET @s2 = "[vV]andali?z?m?"; -- konu2  

SET @n = 3; -- en az kac tane

select @gun;

select concat("[[",replace(page_title,"_"," "),"]]") as ad

,count(*) as kac_kez

, group_concat(rc_user_text SEPARATOR "; ") as kisiler

,max(rc_timestamp) as zaman_son

,concat("tr.wikipedia.org/wiki/Özel:Diff/",max(rc_this_oldid)) as url_son

,group_concat(rc_comment SEPARATOR "; ") as konular

-- ,group_concat(ug_group)

from recentchanges_userindex

join page on page_id = rc_cur_id

join user_groups on ug_user = rc_user

where rc_timestamp > @gun

and page_namespace = 0

and rc_comment rlike @s1

and rc_user_text not rlike "[Cc]ommons[dD]elinker"

and ug_group in ("patroller","sysop")

-- and rc_comment rlike @s2

group by page_id having count(*)>= @n

order by count(*) desc

;

​