select 
    ORDER_DATETIME as ORDER_DATE
  --  ,ORDER_DATETIME2
    ,EO.MEMBER_CODE as CODE
  --  ,julianday(ORDER_DATETIME)-julianday(ORDER_DATETIME2)
    ,case OPTOUT_TYPE when 0 then '可' when 1 then '不可' when 2 then '不明' end  as OPTOUT
from 
    (select * from EC_ORDERS where ORDER_DATETIME>='2023-07-01 00:00:00') EO
    inner join MEMBER_MST MM on EO.MEMBER_CODE=MM.MEMBER_CODE
    inner join (select MEMBER_CODE,ORDER_DATETIME as ORDER_DATETIME2
                from EC_ORDERS where ORDER_DATETIME<'2023-07-01 00:00:00') EE on EO.MEMBER_CODE=EE.MEMBER_CODE
where julianday(ORDER_DATETIME)-julianday(ORDER_DATETIME2)>365
group by CODE
order by ORDER_DATE desc,CODE desc