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