SQL中如何按日期范围左连接合并两个数据集

你遇到的场景本质是：数据集B的每一行Income对应一段连续的有效日期区间，规则为「当前行的StartDate 到 排序后下一行的StartDate前一天」。由于原始B表没有直接存储区间结束点，需要先补全区间范围，再做日期范围的关联匹配。

实现核心规则 #

• 对B表按StartDate排序，取当前行的下一个相邻StartDate作为区间右边界，采用左闭右开的判断规则（即日期≥当前StartDate且<下一个StartDate），避免相邻区间边界日被重复匹配。
• 所有日期字段必须先转换为标准日期类型再做比较，禁止直接用字符串大小判断。

可直接套用的SQL实现 #

不同数据库的字符串转日期函数有差异，替换为对应库的函数即可运行：

-- 日期转换函数映射：
-- MySQL: STR_TO_DATE(日期字符串, '%d/%m/%Y')
-- PostgreSQL: TO_DATE(日期字符串, 'DD/MM/YYYY')
-- SQL Server: CONVERT(DATE, 日期字符串, 103)

WITH B_WITH_VALID_RANGE AS (
    SELECT
        CONVERT(DATE, StartDate, 103) AS start_dt,
        Income,
        -- 按StartDate排序后，取相邻下一个StartDate作为区间右边界
        LEAD(CONVERT(DATE, StartDate, 103), 1) OVER (ORDER BY CONVERT(DATE, StartDate, 103)) AS next_start_dt
    FROM B
)
SELECT
    CONVERT(DATE, A.Date, 103) AS Date,
    A.Age,
    B.Income
FROM A
LEFT JOIN B_WITH_VALID_RANGE B
    ON CONVERT(DATE, A.Date, 103) >= B.start_dt
    -- 右边界为空时表示该区间永久有效，直接匹配所有符合条件的日期
    AND (CONVERT(DATE, A.Date, 103) < B.next_start_dt OR B.next_start_dt IS NULL)

示例数据验证结果 #

代入你给出的测试数据计算：

1. 补全后的B表有效区间列表：
   • start_dt=2015-03-30, Income=1k, next_start_dt=2015-07-31 → 有效范围：2015-03-30 ≤ 日期 < 2015-07-31
   • start_dt=2015-07-31, Income=2k, next_start_dt=2015-10-01 → 有效范围：2015-07-31 ≤ 日期 < 2015-10-01
   • start_dt=2015-10-01, Income=3k, next_start_dt=NULL → 有效范围：日期 ≥2015-10-01
2. A表的匹配结果：
   • 31/3/2015 转换为日期2015-03-31，落在第一个区间，匹配Income=1k
   • 31/8/2015 转换为日期2015-08-31，落在第二个区间，匹配Income=2k
     完全符合预期输出。

兼容旧版本SQL的替代写法 #

如果数据库版本不支持窗口函数LEAD()，可以用关联子查询实现相同的区间补全逻辑：

WITH B_WITH_VALID_RANGE AS (
    SELECT
        b1.start_dt,
        b1.Income,
        MIN(b2.start_dt) AS next_start_dt
    FROM (SELECT CONVERT(DATE, StartDate, 103) AS start_dt, Income FROM B) b1
    LEFT JOIN (SELECT CONVERT(DATE, StartDate, 103) AS start_dt, Income FROM B) b2
        ON b2.start_dt > b1.start_dt
    GROUP BY b1.start_dt, b1.Income
)
-- 后续关联逻辑和上面的SQL完全一致

常见踩坑提醒 #

• 禁止直接用字符串做日期比较：DD/MM/YYYY格式的字符串排序逻辑和实际日期顺序完全错位，比如'30/1/2016'字符串值小于'5/12/2015'，会导致匹配完全错误。
• 不要手动计算区间结束日期为「下一个StartDate - 1天」，这种写法在处理闰年、不同月份天数时容易出错，左闭右开的范围判断不需要计算具体结束日期，逻辑更简洁、容错性更高。

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