SELECT
 ORD.ORDER_DATETIME AS ORDER_DATE
 , ORD.MEMBER_CODE AS CODE
 , CASE MEM.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 FIRST_DATE
    FROM EC_ORDERS
   WHERE DATE(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31'
   GROUP BY MEMBER_CODE
  ) AS ORD
INNER JOIN MEMBER_MST MEM
ON ORD.MEMBER_CODE = MEM.MEMBER_CODE
WHERE 
EXISTS (SELECT ORDER_NO FROM EC_ORDERS WHERE ORD.MEMBER_CODE = MEMBER_CODE AND DATE(ORDER_DATETIME) < DATE(ORD.FIRST_DATE, '-1 year'))
AND NOT EXISTS (SELECT ORDER_NO FROM EC_ORDERS WHERE ORD.MEMBER_CODE = MEMBER_CODE AND DATE(ORDER_DATETIME) BETWEEN DATE(ORD.FIRST_DATE, '-1 year') AND DATE(ORD.FIRST_DATE, '-1 day'))
ORDER BY ORD.ORDER_DATETIME DESC, ORD.MEMBER_CODE DESC