Microsoft Access中基于日期范围连接两个表的技术求助

Hey there! Let's tackle this problem together—joining tables with date ranges where the ranges don't perfectly line up is a pretty common scenario, but the solution depends entirely on how you want the dates to associate between Table 1 and Table 2. Let's walk through the most common cases and the SQL queries to make it work.

First, let's assume some sample table structures (since you didn't share the exact schema, I'll use typical fields you might have):

Sample Table Structures #

Table 1 (Date-Range Descriptions) #

Table 2 (Records to Enrich) #

Scenario 1: Table 2 has a single date, match to Table 1's containing range #

If you want to pull the description from Table 1 where Table 2's event_date falls inside Table 1's date range, use a LEFT JOIN (to keep all Table 2 records, even if no match exists) with a date condition:

SELECT 
  t2.*,
  t1.description
FROM table2 t2
LEFT JOIN table1 t1
  ON t2.event_date BETWEEN t1.range_start AND t1.range_end;

• Use INNER JOIN instead if you only want Table 2 records that have a matching range in Table 1.

Scenario 2: Both tables have date ranges, match overlapping ranges #

If Table 2 also has its own date range (e.g., event_start and event_end), you need to check for any overlap between the two ranges. The logic for overlapping ranges is:

Table 2's start ≤ Table 1's end AND Table 2's end ≥ Table 1's start

Here's the query:

SELECT 
  t2.*,
  t1.description
FROM table2 t2
LEFT JOIN table1 t1
  ON t2.event_start <= t1.range_end
  AND t2.event_end >= t1.range_start;

Handling Multiple Matches #

If Table 1 has multiple ranges that match a single Table 2 record (e.g., overlapping ranges in Table 1), you'll get duplicate Table 2 rows. To fix this, use a window function to pick the "best" match (e.g., the most recent range in Table 1):

SELECT *
FROM (
  SELECT 
    t2.*,
    t1.description,
    -- Assign a rank to matches for each Table 2 record (latest range first)
    ROW_NUMBER() OVER (
      PARTITION BY t2.record_id 
      ORDER BY t1.range_end DESC
    ) AS match_rank
  FROM table2 t2
  LEFT JOIN table1 t1
    ON t2.event_date BETWEEN t1.range_start AND t1.range_end
) ranked_matches
WHERE match_rank = 1; -- Keep only the top-ranked match

Adjust the ORDER BY clause to match your priority (e.g., range_start ASC for the earliest range, or a custom priority column if you have one).

Key Questions to Refine Further #

If none of these fit exactly, ask yourself:

• Should Table 2's range be fully contained within Table 1's range, or just overlap?
• If multiple Table 1 ranges match, which one takes precedence (latest, earliest, etc.)?
• Do you need to handle NULL dates (e.g., open-ended ranges like range_end IS NULL)?

Once you clarify those, you can tweak the queries above to fit your exact needs.

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