交易查询需求：筛选Trans1发生60天后未出现Trans2的记录

Hey there! Let's break down how to solve this query problem step by step—since you're new to this, I'll keep things clear and tie it back to your examples.

First, let's align on the core requirement: we need to pull every Trans 1 record where there are NO corresponding Trans 2 records for the same user/entity within 60 days after the Trans 1 date. Your examples make this super concrete:

• Example A: Trans 1 on 2018-01-01 has Trans 2s on 2018-01-02 and 2018-02-01 (both within 60 days) → we exclude this Trans 1
• Example B: Trans 1 on 2018-01-01 has no Trans 2s between 2018-01-01 and 2018-03-02 (60 days later) → we keep this Trans 1

Assumptions About Your Table #

I'll assume you have a table (let's call it transactions) with these key fields:

• user_id: To link transactions to the same person/entity (critical—we can't compare Trans 1 and Trans 2 from different users!)
• trans_type: The transaction type ('Trans 1' or 'Trans 2')
• trans_date: The date the transaction occurred (should be a DATE/DATETIME type for reliable date calculations)

Method 1: Using NOT EXISTS (Most Intuitive for Beginners) #

This approach checks each Trans 1 record and verifies there's no matching Trans 2 in the 60-day window.

SELECT t1.*
FROM transactions t1
WHERE t1.trans_type = 'Trans 1'
  AND NOT EXISTS (
    -- Subquery: Look for any Trans 2 for the same user in the 60 days after Trans 1
    SELECT 1
    FROM transactions t2
    WHERE t2.user_id = t1.user_id
      AND t2.trans_type = 'Trans 2'
      AND t2.trans_date BETWEEN t1.trans_date AND DATE_ADD(t1.trans_date, INTERVAL 60 DAY)
  );

How This Works:

1. We first filter all records to only get Trans 1 entries (t1.trans_type = 'Trans 1').
2. The NOT EXISTS clause acts as a check: for each Trans 1, if there are NO Trans 2s that meet all three conditions (same user, correct type, date in the window), the Trans 1 is kept.
3. For your Example A, the subquery would find Trans 2s on 2018-01-02 and 2018-02-01, so that Trans 1 is excluded. For Example B, the subquery finds nothing, so the Trans 1 is included.

Method 2: Using LEFT JOIN + IS NULL #

Another common way to solve this is with a left join, then filtering out any Trans 1s that have a matching Trans 2.

SELECT t1.*
FROM transactions t1
LEFT JOIN transactions t2
  ON t2.user_id = t1.user_id
  AND t2.trans_type = 'Trans 2'
  AND t2.trans_date BETWEEN t1.trans_date AND DATE_ADD(t1.trans_date, INTERVAL 60 DAY)
WHERE t1.trans_type = 'Trans 1'
  AND t2.trans_id IS NULL; -- trans_id is a unique primary key for transactions

How This Works:

1. We left join Trans 1 records with any Trans 2s that fit the criteria.
2. If a Trans 1 has no matching Trans 2, all the t2 fields will be NULL. We filter for those cases with t2.trans_id IS NULL.

Important Notes for Beginners #

• Date Functions Vary by Database: The DATE_ADD function works for MySQL. If you're using PostgreSQL, use t1.trans_date + INTERVAL '60 days'; for SQL Server, use DATEADD(day, 60, t1.trans_date).
• Double-Check Your Linking Field: Make sure user_id (or whatever field you use to group transactions) is correct—you don't want to compare Trans 1 from User A with Trans 2 from User B!
• Date Type Matters: Ensure trans_date is stored as a date/time type (not a string) so date comparisons work correctly.

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