如何获取截至2020/7/31购买'a'但后续未购买的客户编号？

Got it, let's refine this query to meet your exact requirements. The original statement only checks for customers who bought item 'a' exactly on July 31, 2020, but we need to include anyone who purchased 'a' on or before that date, and ensure they had no purchases whatsoever after that cutoff. Here's the optimized version using IN and NOT IN logic:

SELECT DISTINCT customer_number
FROM your_table_name
WHERE customer_number IN (
    -- Fetch all customers who bought 'a' on or before July 31, 2020
    SELECT customer_number
    FROM your_table_name
    WHERE purchased = 'a'
      AND date <= '2020-07-31'
)
AND customer_number NOT IN (
    -- Exclude customers who made any purchase after July 31, 2020
    SELECT customer_number
    FROM your_table_name
    WHERE date > '2020-07-31'
);

Breakdown of the logic: #

• IN subquery: This captures every customer who has at least one purchase of item 'a' on or before the cutoff date. Using <= instead of = expands the scope to include all prior purchases of 'a', which aligns with your requirement.
• NOT IN subquery: This filters out any customer who had any purchase (regardless of the item) after July 31, 2020. We don't care what they bought here—we just need to exclude anyone with post-cutoff activity.
• DISTINCT: Ensures we don't get duplicate customer numbers if a customer bought 'a' multiple times before the cutoff.

A quick note: Depending on your database system, you might need to adjust the date format (some prefer '07-31-2020' but '2020-07-31' is the ISO standard and works across most systems like PostgreSQL, MySQL, and SQL Server). Swap it if your database expects a different format.

内容的提问来源于stack exchange，提问作者user14263992