select
    sub.order_datetime as ORDER_DATE
    , sub.member_code as CODE
    , case mm.optout_type
        when 0 then '可'
        when 1 then '不可'
        else '不明'
        end as OPTOUT
from (
    select
        member_code
        , last_name
        , first_name
        , min(order_datetime) as order_datetime
        , min(date(order_datetime)) as m_date
    from ec_orders
    where
        date(order_datetime) between '2023-07-01' and '2023-07-31'
    group by member_code
    ) as sub
    inner join member_mst mm on sub.member_code=mm.member_code
where 
exists(
    select order_no
    from ec_orders
    where sub.member_code=member_code
    and   date(order_datetime)<date(sub.m_date,'-1 year')
)
and
not exists(
    select order_no
    from ec_orders
    where sub.member_code=member_code
    and   date(order_datetime) between date(sub.m_date,'-1 year') and date(sub.m_date,'-1 day')
)
order by sub.order_datetime desc, sub.member_code desc;