WITH JULY AS (SELECT MIN(ORDER_DATETIME) AS ORDER_DATE, MEMBER_CODE
FROM EC_ORDERS
WHERE ORDER_DATETIME BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59' GROUP BY MEMBER_CODE),
PRE AS (SELECT MAX(ORDER_DATETIME) AS ORDER_DATE, MEMBER_CODE
FROM EC_ORDERS WHERE ORDER_DATETIME < '2023-07-01 00:00:00' GROUP BY MEMBER_CODE)
SELECT JULY.ORDER_DATE, MEMBER_CODE AS CODE, CASE OPTOUT_TYPE WHEN 0 THEN '可' WHEN 1 THEN '不可' ELSE '不明' END AS OPTOUT FROM JULY JOIN MEMBER_MST USING (MEMBER_CODE)
WHERE EXISTS(SELECT 1 FROM PRE WHERE JULY.MEMBER_CODE = PRE.MEMBER_CODE AND DATE(PRE.ORDER_DATE) < DATE(JULY.ORDER_DATE, '-1 year'))
ORDER BY 1 DESC, 2 DESC;