SELECT
 EO.ORDER_DATETIME AS ORDER_DATE,
 EO.MEMBER_CODE AS CODE,
 CASE MM.OPTOUT_TYPE WHEN 0 THEN '可' WHEN 1 THEN '不可' ELSE '不明'  END AS OPTOUT
FROM
(
SELECT
 DISTINCT EO2.ORDER_DATETIME,
          EO2.MEMBER_CODE
FROM
(
SELECT
 MAX(ORDER_DATETIME) AS ORDER_DATETIME,
 MEMBER_CODE
FROM
 EC_ORDERS
WHERE
 ORDER_DATETIME < '2023-07-01'
GROUP BY MEMBER_CODE
) AS EO1
INNER JOIN 
(
SELECT
 MIN(ORDER_DATETIME) AS ORDER_DATETIME,
 MEMBER_CODE
FROM
 EC_ORDERS
WHERE 
 ORDER_DATETIME BETWEEN '2023-07-01' AND '2023-07-31 23:59:59'
GROUP BY MEMBER_CODE
) AS EO2
ON EO1.MEMBER_CODE = EO2.MEMBER_CODE AND DATE(EO1.ORDER_DATETIME) < DATE(EO2.ORDER_DATETIME, '-1 YEARS')
) AS EO
INNER JOIN MEMBER_MST AS MM
ON EO.MEMBER_CODE = MM.MEMBER_CODE
ORDER BY EO.ORDER_DATETIME DESC, EO.MEMBER_CODE DESC