SELECT EC_ORDERS.ORDER_DATETIME ORDER_DATE
,   EC_ORDERS.MEMBER_CODE CODE
,   CASE WHEN IFNULL(OPTOUT_TYPE,'') = 0 THEN '可'
        WHEN IFNULL(OPTOUT_TYPE,'') = 1 THEN '不可'
        ELSE '不明' END OPTOUT
FROM EC_ORDERS
INNER JOIN (SELECT MEMBER_CODE
            ,   MAX(ORDER_DATETIME) MAE_DATETIME
            FROM EC_ORDERS
            WHERE ORDER_DATETIME NOT LIKE '2023-07%'
            GROUP BY MEMBER_CODE
            ) A
ON A.MEMBER_CODE = EC_ORDERS.MEMBER_CODE
LEFT JOIN MEMBER_MST
ON MEMBER_MST.MEMBER_CODE = EC_ORDERS.MEMBER_CODE
WHERE EC_ORDERS.ORDER_DATETIME LIKE '2023-07%'
AND cast(julianday(EC_ORDERS.ORDER_DATETIME)-julianday(MAE_DATETIME) as integer) >= 365
ORDER BY EC_ORDERS.ORDER_DATETIME DESC
,   EC_ORDERS.MEMBER_CODE DESC