如何基于日期范围相交实现两表关联并匹配首个相交记录

问题分析 #

你的需求核心：

• 基于ID关联表T1与Mars_Crater_Names（别名N1）
• 仅保留两个表中日期范围存在重叠的记录
• 对每个T1记录，匹配按N1日期顺序最早的相交记录

原SQL的问题在于相交条件判断错误：仅用t1.StartDate between n1.StartDate and n1.EndDate会漏掉多种重叠场景（比如T1的日期范围部分覆盖N1区间）。日期范围相交的正确逻辑是：两个区间[T1.StartDate, T1.EndDate]和[N1.StartDate, N1.EndDate]存在重叠，需满足t1.StartDate <= n1.EndDate AND n1.StartDate <= t1.EndDate。

解决方案 #

使用窗口函数ROW_NUMBER()，先关联所有符合条件的记录，再对每个T1对应的N1结果排序，筛选出排序第一的记录（即首个相交的N1记录）。

完整SQL语句 #

WITH matched_records AS (
    SELECT 
        t1.*,
        n1.CraterName,
        n1.StartDate AS Crater_StartDate,
        n1.EndDate AS Crater_EndDate,
        -- 按N1的StartDate升序，确保最早相交的记录排在首位
        ROW_NUMBER() OVER (
            PARTITION BY t1.ID, t1.StartDate, t1.EndDate 
            ORDER BY n1.StartDate ASC
        ) AS rn
    FROM T1
    INNER JOIN Mars_Crater_Names n1 
        ON t1.ID = n1.ID
        -- 日期范围相交的正确判断条件
        AND t1.StartDate <= n1.EndDate 
        AND n1.StartDate <= t1.EndDate
)
SELECT 
    ID,
    Stellar_Phase,
    StartDate,
    EndDate,
    CraterName,
    Crater_StartDate,
    Crater_EndDate
FROM matched_records
WHERE rn = 1;

逻辑说明 #

1. CTE matched_records：先关联所有ID匹配且日期范围相交的记录，同时用ROW_NUMBER()给每个T1记录对应的N1记录编号。分组依据用ID, StartDate, EndDate确保每个T1记录单独分组，排序依据用N1的StartDate升序，保证最早的相交N1记录被标记为rn=1。
2. 最终查询：筛选出rn=1的记录，即每个T1记录对应的首个相交N1记录。

测试验证 #

结合你提供的示例数据：

• T1的Drying记录（18120000-18330000）与N1的Phoenix、Aurora都相交，按N1的StartDate排序后Phoenix的rn=1，会被选中。
• T1的Normalizing记录（18330000-19780000）仅与N1的Aurora相交，rn=1，会被选中。

结果完全符合你的期望。

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