我正在try 使用以下查询列出在过go 30天内没有购买的所有客户:
SELECT c.*
FROM customers c
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));
这将产生以下输出:
| CUSTOMER_ID | FIRST_NAME | LAST_NAME |
|---|---|---|
| 1 | Faith | Mazzarone |
| 2 | Lisa | Saladino |
此查询似乎正在运行.
但我也希望显示最后的"purchase_date"值以及客户信息，或者如果客户从未购买过产品，则显示NULL.我似乎想不出该怎么做.
有没有人能帮帮我？
以下是重现我的环境的DDL:
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, '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;