您可以使用ROW_NUMBER窗口函数来检测每个客户的最后购买日期，然后重新连接到customers信息以获得每个客户的全名。

WITH cte AS (
    SELECT CUSTOMER_ID,
           PURCHASE_DATE,
           ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY PURCHASE_DATE DESC) AS rn
    FROM purchases
)
SELECT c.*, cte.PURCHASE_DATE
FROM       customers c
LEFT JOIN cte ON c.CUSTOMER_ID = cte.CUSTOMER_ID
             AND cte.PURCHASE_DATE < TRUNC(SYSTIMESTAMP) - 30
             AND cte.rn = 1

检查here演示。

您可以使用MAX（）函数获取每个客户的最近购买日期，然后将该信息与customers表联接，从而显示上次购买日期和客户信息。

SELECT c.*, p.last_purchase
FROM customers c
LEFT JOIN (SELECT customer_id, MAX(purchase_date) as last_purchase
           FROM purchases
           GROUP BY customer_id) p
ON c.customer_id = p.customer_id
WHERE NOT EXISTS (SELECT 1
                  FROM purchases p 
                  WHERE c.customer_id  = p.customer_id AND                                              
                  p.PURCHASE_DATE >= TRUNC(SYSTIMESTAMP) - NUMTODSINTERVAL (30, 'DAY') AND
                  p.PURCHASE_DATE < TRUNC(SYSTIMESTAMP)  
                 );

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

 ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Micheal', 'Palmice' FROM DUAL UNION ALL
SELECT 4, 'Jerry', 'Torchiano' FROM DUAL;

CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Black Shoes', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Brown Pants', 111.99 FROM DUAL UNION ALL
SELECT 102, 'White Shirt', 10.99 FROM DUAL;

CREATE TABLE purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) AS
SELECT 1, 101, 3, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-12 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2, 101, 3, TIMESTAMP '2022-10-17 19:34:58' FROM DUAL UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-12-11 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-12-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-12-12 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 5;


select  c.customer_id,
        c.first_name,
        c.last_name,
        max(p.purchase_date) last_purchase
  from  customers c,
        purchases p
  where p.customer_id(+) = c.customer_id
  group by c.customer_id,
           c.first_name,
           c.last_name
  having max(purchase_date) < trunc(systimestamp) - 30
      or max(purchase_date) is null

CUSTOMER_ID FIRST_NAME  LAST_NAME   LAST_PURCHASE
1   Faith   Mazzarone   12-OCT-2022  19:04:18.000000
2   Lisa    Saladino    17-OCT-2022  19:34:58.000000
4   Jerry   Torchiano    -