多级父子表关系下如何查找相对最新的子记录

问题背景 #

存在如下多级关联的表结构：

需求为user_hist表中每一条用户记录，匹配对应的contract.contract_id，要求关联链路涉及的employer_contract_hist、contract_hist、contract三张表，筛选出的记录last_changed_date必须是早于当前用户记录user_hist.audit_date的最新有效值。

原有初步SQL写法存在逻辑错误、执行性能差的问题，参考写法如下：

SELECT 
    u.user_id,
    c.contract_id
FROM 
    user_hist u,
    employer_contract_hist ech,
    contract_hist ch,
    contract c
WHERE u.employer_id = ech.employer_id_fk
AND ech.last_changed_date = (select max(ech2.last_changed_date)
                            from employer_contract_hist ech2
                            where ech2.last_changed_date < u.audit_date
                            and ech2.employer_id_fk = ech.employer_id_fk
                            and ech2.contract_hist_fk = ech.contract_hist_fk)
AND ech.contract_hist_fk = ch.contract_hist_id
AND ch.last_changed_date = (select max(ch2.last_changed_date)
                            from contract_hist ch2
                            where ch2.last_changed_date < u.audit_date
                            and ch2.contract_hist_id = ch.contract_hist_id
                            and ch2.contract_id_fk = ch.contract_id_fk)
AND c.contract_id = ch.contract_hist_id
AND c.last_changed_date = (select max(c2.last_changed_date)
                            from contract c2
                            where c2.last_changed_date < u.audit_date
                            and c2.contract_id = c.contract_id)

原写法的核心问题 #

• 关联字段错误：c.contract_id = ch.contract_hist_id属于字段映射错误，正确关联条件应为ch.contract_id_fk = c.contract_id
• 子查询逻辑偏差：以employer_contract_hist的筛选子查询为例，额外增加了ech2.contract_hist_fk = ech.contract_hist_fk的限定条件，无法按雇主维度筛选出全局最新的关联记录，不符合需求
• 性能低下：三层嵌套的相关子查询会针对主查询的每一行反复执行全表扫描，数据量级超过10万后执行延迟会非常明显

推荐实现方案（窗口函数版） #

只要使用的数据库支持窗口函数（MySQL 8.0+、PostgreSQL 10+、Oracle 12c+、SQL Server 2012+均支持），优先用ROW_NUMBER()窗口函数做分区排序取最新值，通过CTE拆分每一层的筛选逻辑，可读性和性能都远高于原写法：

WITH
-- 筛选每个雇主在对应用户审计时间点前的最新关联记录
valid_ech AS (
    SELECT
        u.user_id,
        u.audit_date,
        ech.contract_hist_fk,
        ROW_NUMBER() OVER (
            PARTITION BY u.user_id
            ORDER BY ech.last_changed_date DESC
        ) AS rn
    FROM user_hist u
    INNER JOIN employer_contract_hist ech
        ON u.employer_id = ech.employer_id_fk
        AND ech.last_changed_date < u.audit_date
),
-- 基于上一步结果，筛选匹配的最新contract_hist记录
valid_ch AS (
    SELECT
        ve.user_id,
        ve.audit_date,
        ch.contract_id_fk,
        ROW_NUMBER() OVER (
            PARTITION BY ve.user_id
            ORDER BY ch.last_changed_date DESC
        ) AS rn
    FROM valid_ech ve
    INNER JOIN contract_hist ch
        ON ve.contract_hist_fk = ch.contract_hist_id
        AND ch.last_changed_date < ve.audit_date
    WHERE ve.rn = 1
),
-- 基于上一步结果，筛选匹配的最新contract记录
valid_c AS (
    SELECT
        vc.user_id,
        c.contract_id,
        ROW_NUMBER() OVER (
            PARTITION BY vc.user_id
            ORDER BY c.last_changed_date DESC
        ) AS rn
    FROM valid_ch vc
    INNER JOIN contract c
        ON vc.contract_id_fk = c.contract_id
        AND c.last_changed_date < vc.audit_date
    WHERE vc.rn = 1
)
-- 输出最终结果
SELECT user_id, contract_id
FROM valid_c
WHERE rn = 1;

方案优势 #

• 逻辑分层清晰：每一层CTE只处理单表的最新值筛选，没有冗余条件，排查问题成本低
• 执行效率高：窗口函数仅需对各关联表做一次分区排序计算，避免了相关子查询的逐行反复扫描，数据量越大性能优势越明显
• 扩展性强：后续如果需要新增其他历史表关联，只需要新增对应CTE节点即可，不需要嵌套多层复杂子查询

如果使用的是不支持窗口函数的老旧数据库版本（如MySQL 5.x），可以先通过GROUP BY聚合出各分组下的最大last_changed_date，再通过等值关联回原表取记录，性能也会优于原有的相关子查询写法。

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