SQL多表关联：基于表A开始日期匹配表B历史等级的查询实现

首先明确核心匹配规则：

• 两表的基础关联字段是用户唯一标识IDNO，用于定位同一用户的跨表数据
• 日期匹配不采用等值匹配：因为历史等级是按时间迭代生效的，需要为每条表A的记录，找到同一用户下、表B中等级生效日期早于/等于表AStart_Date的最新一条记录，取其对应的Rank值——这也是和示例输出完全对齐的匹配逻辑。

匹配逻辑对照：
SJ01的Start_Date为2016-11-13，表B中他的等级更新节点为2010-10-01（CAP）、2016-10-01（BC）、2020-10-01（AC）。其中早于2016-11-13的最近一次等级更新是2016-10-01的BC，正好对应示例结果的Rank值。
DJ01的Start_Date为2020-10-20，表B中他早于该日期的最近等级更新是2020-01-01的BC，同样匹配示例输出。

具体实现方式 #

方式1：窗口函数实现（主流数据库通用，性能更优） #

适用于MySQL 8.0+、PostgreSQL、SQL Server、Oracle等支持窗口函数的数据库，逻辑清晰，避免子查询的重复扫描：

WITH matched_records AS (
    SELECT
        a.IDNO,
        a.LName,
        a.FName,
        a.Start_Date,
        a.PayType,
        b.Rank,
        -- 按匹配到的历史等级日期倒序，取排名第1的最新记录
        ROW_NUMBER() OVER (
            PARTITION BY a.IDNO, a.Start_Date
            ORDER BY b.Date DESC
        ) AS match_rank
    FROM Table_A a
    LEFT JOIN Table_B b
        -- 基础关联：同一用户
        ON a.IDNO = b.IDNO
        -- 核心过滤：仅保留Start_Date之前生效的历史等级
        AND b.Date <= a.Start_Date
)
-- 过滤取最新的匹配等级
SELECT IDNO, LName, FName, Start_Date, PayType, Rank
FROM matched_records
WHERE match_rank = 1;

方式2：相关子查询实现（兼容低版本数据库） #

适用于MySQL 5.x等不支持窗口函数的旧版本数据库，逻辑直观：

SELECT
    a.IDNO,
    a.LName,
    a.FName,
    a.Start_Date,
    a.PayType,
    -- 子查询定位当前用户下，早于Start_Date的最新等级
    (
        SELECT b.Rank
        FROM Table_B b
        WHERE
            b.IDNO = a.IDNO
            AND b.Date <= a.Start_Date
        ORDER BY b.Date DESC
        LIMIT 1
    ) AS Rank
FROM Table_A a;

容易踩的坑 #

• 不要直接用日期等值关联：示例里两表的日期本身就不存在完全相等的情况（SJ01的Start_Date是11/13/2016，匹配的历史记录日期是10/01/2016），等值关联会查不到任何结果。
• 关联条件不能省略IDNO匹配：如果只按日期关联，会把不同用户同时间段的等级记录错配，导致结果完全错误。
• 日期字段要做类型转换：如果两个表的日期是字符串存储（比如示例里的mm/dd/yyyy格式），必须先统一转成日期类型再做大小比较，否则字符串排序的规则和日期排序不一致，会出现比较结果错误的问题。

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