用于查询存废编辑的简单脚本

SQL

USE zhwiki_p;
select p.page_title,rc.rc_timestamp,DATE_FORMAT(rc.rc_timestamp,'%Y/%m/%d %H:%i'),rc.rc_id
from recentchanges rc 
inner join revision rv on rc.rc_this_oldid = rv.rev_id 
inner join page p on rv.rev_page = p.page_id
left join revision_actor_temp rat on rat.revactor_rev=rv.rev_id
where /*rc.rc_type in ('0','1')
and */
(
  rc.rc_actor in  (
    select ac.actor_id 
    from actor ac
    where ac.actor_user in (
      select u.user_id 
      from user u 
      where u.user_name='Hamish'
    )
  ) or 
  rat.revactor_actor in (
    select ac.actor_id 
    from actor ac
    where ac.actor_user in (
      select u.user_id 
      from user u 
      where u.user_name='Hamish'
    )    
  )     
)
and (
    rc.rc_timestamp > UNIX_TIMESTAMP('2017-09-22') or 
    rv.rev_timestamp> UNIX_TIMESTAMP('2017-09-22')
)
and rv.rev_page in (
    select _p.page_id
    from page _p 
    where _p.page_title like '頁面存廢討論/記錄/%'  and
    _p.page_namespace='4'
)
order by rc.rc_timestamp desc;
SQL

Checking query status...
