SELECT DATE(ORDER_DATETIME) AS ORDER_DATE, CODE,
CASE WHEN OPTOUT_TYPE = 0 THEN '可'
WHEN OPTOUT_TYPE = 1 THEN '不可'
ELSE '不明' END AS OPTOUT
FROM (SELECT MIN(ORDER_DATETIME) AS ORDER_DATETIME,
MIN(DATE(ORDER_DATETIME)) AS M_DATE, MEMBER_CODE AS CODE
FROM EC_ORDERS
WHERE DATE(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31'
GROUP BY CODE) AS SUB
INNER JOIN MEMBER_MST MM
ON SUB.CODE = MM.MEMBER_CODE
WHERE EXISTS
(SELECT ORDER_NO FROM EC_ORDERS
WHERE SUB.CODE = MEMBER_CODE
AND DATE(ORDER_DATETIME) < DATE(SUB.M_DATE, '-1 year'))
AND NOT EXISTS
(SELECT ORDER_NO FROM EC_ORDERS
WHERE SUB.CODE = MEMBER_CODE
AND DATE(ORDER_DATETIME) BETWEEN DATE(SUB.M_DATE, '-1 year') AND DATE(SUB.M_DATE, '-1 day'))
ORDER BY ORDER_DATE DESC, CODE DESC