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

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 page p on rc.rc_cur_id = p.page_id
inner join revision rv on rc.rc_this_oldid = 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 
  rc.rc_this_oldid in (
    select revactor_rev
    from revision_actor_temp rat
    where 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_namespace='4'*/
and (
    rc.rc_timestamp > UNIX_TIMESTAMP('2017-09-22') or 
    rv.rev_timestamp> UNIX_TIMESTAMP('2017-09-22')
)
and p.page_id 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...
