SELECT 
B.MINDATE AS ORDER_DATE
,B.MEMBER_CODE AS CODE
,case C.OPTOUT_TYPE WHEN '0' THEN '可' WHEN '1' THEN '不可' ELSE '不明' END AS OPTOUT
FROM 
(
SELECT MEMBER_CODE,MAX(ORDER_DATETIME) MAXDATE
FROM EC_ORDERS
WHERE date(ORDER_DATETIME) < date('2023-07-01')
GROUP BY MEMBER_CODE
) A
INNER JOIN (
SELECT MEMBER_CODE,MIN(ORDER_DATETIME) MINDATE
FROM EC_ORDERS
WHERE date(ORDER_DATETIME)>=date('2023-07-01 00:00:00')
AND date(ORDER_DATETIME)<date('2023-08-01')
GROUP BY MEMBER_CODE
) B
ON A.MEMBER_CODE=B.MEMBER_CODE
AND DATE(MAXDATE,'+1 years')< DATE(MINDATE)
INNER JOIN MEMBER_MST C
ON A.MEMBER_CODE =C.MEMBER_CODE
order BY 1 DESC,2 DESC