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