如何基于有效日期范围关联Aircraft与Seat表并生成新有效区间

问题背景 #

现有两张带有效日期范围的表：

Aircraft表 #

Seat表 #

需要将两张表关联，生成覆盖所有时间段、且匹配对应有效区间数据的新记录，最终目标结果如下：

目标结果表 #

解决方案 #

核心思路是先提取所有关键时间节点，用这些节点切割出连续的时间区间，再将每个区间与两张表匹配，获取对应时间段的Aircraft和Seat值。以下是通用SQL实现（适配MySQL、PostgreSQL、SQL Server等主流关系型数据库）：

WITH all_dates AS (
    -- 提取Aircraft表的所有日期节点
    SELECT LegID, AFrom AS date_point FROM Aircraft
    UNION
    SELECT LegID, ATo AS date_point FROM Aircraft
    UNION
    -- 提取Seat表的所有日期节点
    SELECT LegID, SFrom AS date_point FROM Seat
    UNION
    SELECT LegID, STo AS date_point FROM Seat
),
date_ranges AS (
    -- 按LegID分组，将日期节点排序并生成连续区间
    SELECT 
        LegID,
        date_point AS range_from,
        LEAD(date_point) OVER (PARTITION BY LegID ORDER BY date_point) AS range_to
    FROM all_dates
)
-- 关联原表，获取每个区间对应的Aircraft和Seat
SELECT 
    dr.LegID,
    a.Aircraft,
    s.Seat,
    dr.range_from AS `From`,
    dr.range_to AS `To`
FROM date_ranges dr
LEFT JOIN Aircraft a 
    ON dr.LegID = a.LegID 
    AND dr.range_from >= a.AFrom 
    AND dr.range_to <= a.ATo
LEFT JOIN Seat s 
    ON dr.LegID = s.LegID 
    AND dr.range_from >= s.SFrom 
    AND dr.range_to <= s.STo
WHERE dr.range_to IS NOT NULL; -- 过滤无后续节点的无效记录

逻辑说明 #

1. 抓全所有关键时间点：把两张表里所有的起始、结束日期都捞出来，这些点就是分割时间的边界。
2. 拼出连续时间区间：按LegID分组后，把时间点按顺序排列，每个点和它的下一个点组成一个完整的时间段。
3. 匹配对应数据：用生成的每个时间段分别去和Aircraft、Seat表比对，判断该时间段是否落在对应数据的有效期内，匹配上就取对应值，没匹配上则显示NULL。

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