select
    A.ORDER_DATE,
    A.MEMBER_CODE CODE,
    case A.OPTOUT_TYPE
        WHEN 0 THEN '可'
        WHEN 1 THEN '不可'
        ELSE '不明'
    END AS OPTOUT
FROM
        (
            SELECT 
                MIN(a.ORDER_DATETIME) ORDER_DATE,
                b.MEMBER_CODE MEMBER_CODE,
                MIN(b.OPTOUT_TYPE) OPTOUT_TYPE
            FROM
                EC_ORDERS a
                inner join MEMBER_MST b
                ON a.MEMBER_CODE = b.MEMBER_CODE
                inner join (SELECT MEMBER_CODE,Max(ORDER_DATETIME) DATETIME from (select MEMBER_CODE,ORDER_DATETIME from EC_ORDERS where ORDER_DATETIME < '2023-07-01 00:00:00') GROUP by MEMBER_CODE) c
                ON a.MEMBER_CODE = c.MEMBER_CODE
            WHERE
                a.ORDER_DATETIME >= '2023-07-01 00:00:00'
                and a.ORDER_DATETIME <= '2023-07-31 00:00:00'
                and DATETIME(c.DATETIME,'1 years') <= a.ORDER_DATETIME
            GROUP BY b.MEMBER_CODE
        ) A
    ORDER BY A.ORDER_DATE DESC,A.MEMBER_CODE DESC