SELECT
    SUB.ORDER_DATETIME AS ORDER_DATE,
    SUB.MEMBER_CODE AS CODE,
    CASE M.OPTOUT_TYPE
        WHEN 0 THEN '可'
        WHEN 1 THEN '不可'
        ELSE '不明'
    END AS OPTOUT
FROM
    (
        SELECT
            MEMBER_CODE,
            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 M ON SUB.MEMBER_CODE = M.MEMBER_CODE
WHERE
    EXISTS (
        SELECT
            ORDER_NO
        FROM
            EC_ORDERS
        WHERE
            SUB.MEMBER_CODE = MEMBER_CODE
            AND DATE(ORDER_DATETIME) < DATE(SUB.M_DATE, '-1 year')
    )
    AND NOT EXISTS (
        SELECT
            ORDER_NO
        FROM
            EC_ORDERS
        WHERE
            SUB.MEMBER_CODE = MEMBER_CODE
            AND DATE(ORDER_DATETIME) BETWEEN DATE(SUB.M_DATE, '-1 year') AND DATE(SUB.M_DATE, '-1 day')
    )
ORDER BY
    ORDER_DATE DESC,
    CODE DESC;