如何关联P_NAMES与P_HIST表，按生效日期匹配员工历史对应姓名？

不需要创建新表，直接通过关联查询就能实现自动匹配，以下两种高效方案供你选择：

方案一：使用窗口函数匹配最新生效姓名 #

利用ROW_NUMBER()窗口函数，为每个员工的姓名记录按生效日期倒序排序，筛选出对应职位记录日期时最新生效的姓名：

SELECT 
    ph.EMPLID,
    ph.EFFDT AS 职位生效日期,
    ph.NAME AS 职位表存储姓名, -- 若职位表NAME为旧数据可忽略该字段
    pn.NAME AS 对应日期的实际姓名,
    pn.EFFDT AS 姓名生效日期
FROM 
    [P_HIST] ph
LEFT JOIN (
    SELECT 
        EMPLID,
        EFFDT,
        NAME,
        -- 按员工分组，生效日期倒序排名，最新的记录排第1位
        ROW_NUMBER() OVER (PARTITION BY EMPLID ORDER BY EFFDT DESC) AS rn
    FROM 
        [P_NAMES]
    WHERE 
        EFFDT <= ph.EFFDT -- 仅筛选职位日期前已生效的姓名记录
) pn ON ph.EMPLID = pn.EMPLID AND pn.rn = 1

方案二：通过自连接构建姓名生效区间 #

先为每条姓名记录计算生效结束日期（下一条姓名记录的生效日期减1天，最后一条记录的结束日期设为9999-12-31），再关联职位记录的生效日期是否落在该区间内：

SELECT 
    ph.EMPLID,
    ph.EFFDT AS 职位生效日期,
    pn.NAME AS 对应日期的实际姓名,
    pn.EFFDT AS 姓名生效起始日期,
    ISNULL(pn_next.EFFDT - 1, '9999-12-31') AS 姓名生效结束日期
FROM 
    [P_HIST] ph
JOIN [P_NAMES] pn ON ph.EMPLID = pn.EMPLID
LEFT JOIN [P_NAMES] pn_next 
    ON pn.EMPLID = pn_next.EMPLID 
    AND pn_next.EFFDT > pn.EFFDT -- 匹配当前姓名之后的下一条姓名记录
WHERE 
    ph.EFFDT BETWEEN pn.EFFDT AND ISNULL(pn_next.EFFDT - 1, '9999-12-31')

性能优化建议 #

• 为P_NAMES和P_HIST表的EMPLID字段建立单独索引，或创建(EMPLID, EFFDT)复合索引，可大幅提升关联查询效率。
• 若需频繁查询该结果，可将上述查询创建为视图，后续直接调用视图即可，无需重复编写SQL。

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