with ju as(SELECT MIN(ORDER_DATETIME) ORDER_DATE,MEMBER_CODE CODE FROM EC_ORDERS where ORDER_DATETIME like '2023-07%' group by MEMBER_CODE),
juo as(SELECT MIN(ORDER_DATETIME) MOD,MEMBER_CODE CODE FROM EC_ORDERS where substr(ORDER_DATETIME,0,7) < '2023-07' group by MEMBER_CODE)

select JU.*,
  CASE MM.OPTOUT_TYPE
    WHEN 0 THEN '可'
    WHEN 1 THEN '不可'
    ELSE '不明'
  END OPTOUT
  --,DATE(JU.MOD, '-12 months'),JUO.MOD te
FROM Ju
left JOIN MEMBER_MST MM  ON JU.CODE = MM.MEMBER_CODE
left JOIN JUO ON JU.CODE=JUO.CODE
where JUO.MOD < DATE(JU.ORDER_DATE, '-12 months')
order by JU.ORDER_DATE desc,2 desc