SELECT
 A.ORDER_DATE
,A.MEMBER_CODE
,CASE C.OPTOUT_TYPE
  WHEN 0 THEN '可'
  WHEN 1 THEN '不可'
  ELSE '不明'
 END AS OPTOUT
FROM
(
SELECT
 MEMBER_CODE,
 MIN(ORDER_DATETIME) AS ORDER_DATE
FROM EC_ORDERS
WHERE ORDER_DATETIME >= datetime('2023-07-01 00:00:00')
GROUP BY
 MEMBER_CODE
) A
INNER JOIN
(
SELECT
 MEMBER_CODE,
 MAX(ORDER_DATETIME) AS ORDER_DATE
FROM EC_ORDERS
WHERE ORDER_DATETIME <= datetime('2023-06-30 23:59:59')
GROUP BY
 MEMBER_CODE
) B
ON
 A.MEMBER_CODE = B.MEMBER_CODE
INNER JOIN
 MEMBER_MST AS C
ON
 C.MEMBER_CODE = A.MEMBER_CODE
WHERE
 date(B.ORDER_DATE) < date(datetime(A.ORDER_DATE, '-1 years'))
ORDER BY
 A.ORDER_DATE DESC
,A.MEMBER_CODE DESC