SELECT
    T2.ORDER_ANS AS ORDER_DATE
    ,T2.MEMBER_CODE AS CODE
    ,CASE WHEN T3.OPTOUT_TYPE=0 THEN '可'
          WHEN T3.OPTOUT_TYPE=1 THEN '不可'
          ELSE '不明'
    END AS OPTOUT
FROM
(SELECT
	DATE(MAX(ORDER_DATETIME),'+1 YEAR') AS MAE_ORDER
	,MEMBER_CODE
FROM EC_ORDERS
WHERE DATE(ORDER_DATETIME) < '2023-07-01'
GROUP BY MEMBER_CODE
) T1,
(SELECT
	DATE(MIN(ORDER_DATETIME)) AS JULY_ORDER
	,MIN(ORDER_DATETIME) AS ORDER_ANS
	,MEMBER_CODE
FROM EC_ORDERS
WHERE DATE(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31'
GROUP BY MEMBER_CODE
) T2,
MEMBER_MST AS T3
WHERE T1.MEMBER_CODE = T2.MEMBER_CODE
  AND T1.MAE_ORDER < T2.JULY_ORDER
  AND T1.MEMBER_CODE = T3.MEMBER_CODE
ORDER BY T2.ORDER_ANS DESC
        ,T2.MEMBER_CODE DESC
;