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 AS 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;