end0tknr's kipple - 新web写経開発

http://d.hatena.ne.jp/end0tknr/ から移転しました

mysql? ユーザID毎の履歴を持つテーブルの最新レコードでjoin

例えば、次の通り

select
  hm.member_id,
  hm.member_sid,
  hm.jogai_flag,
  hh.regist_date as hgw_update_date,
  coalesce(hh.pv1,0)+coalesce(hh.pv2,0) as pv_size
from xxxx_member hm
join hgw_history hh
  on (hm.member_id=hh.member_id and
      hh.regist_date = (select max(hh2.regist_date)
                        from hgw_history as hh2
                        where hh.member_id=hh2.member_id))
where
  hm.update_date<? and
  hm.member_id not in
    (select sr.member_id from stat_result sr
     where (sr.stat_package in('NoMeasure','CtPolarity')) and
           ?<=chk_date and chk_date<=?) and
  hm.jogai_flag not in ('1','2','3','4') and
  coalesce(hh.pv1,0)+coalesce(hh.pv2,0) > 0
order by hh.member_id