select
   F.ORDER_DATE
 , F.MEMBER_CODE as CODE
 , case
     when OPTOUT_TYPE = 0 then '可'
     when OPTOUT_TYPE = 1 then '不可'
     else '不明'
   end as OPTOUT
from
  (select
      min(ORDER_DATETIME) as ORDER_DATE
    , MEMBER_CODE 
   from 
     EC_ORDERS
   where
     ORDER_DATETIME between '2023-07-01 00:00:00' and '2023-07-31 23:59:59'
   group by
     MEMBER_CODE) as F

  inner join
  (select
     max(ORDER_DATETIME) as ORDER_DATE
   , MEMBER_CODE 
  from 
    EC_ORDERS
  where
    ORDER_DATETIME < '2023-07-01 00:00:00'
  group by
    MEMBER_CODE) as L
  on  L.MEMBER_CODE = F.MEMBER_CODE
  and date(L.ORDER_DATE) < date(datetime(F.ORDER_DATE, '-1 years'))

  inner join MEMBER_MST as M on M.MEMBER_CODE = F.MEMBER_CODE 
order by
   F.ORDER_DATE desc
 , F.MEMBER_CODE desc;