如何关联字段值映射不同的两张表并获取最新记录？

可以通过窗口函数筛选最新记录 + LEFT JOIN关联映射关系实现需求，具体SQL语句如下：

SELECT 
    ed.id,
    ed.event,
    ds.date AS `Date`,
    -- 按需添加两张表的其他字段，例如 ed.xxx, ds.xxx
    ed.`...` AS estimate_details_fields,
    ds.`...` AS delivery_service_fields
FROM estimate_details ed
LEFT JOIN (
    -- 子查询：筛选delivery_service中每个id+source组合的最新记录
    SELECT 
        id,
        source,
        date,
        -- 按需添加delivery_service的其他字段
        `...`
    FROM (
        SELECT 
            *,
            -- 按id和source分组，按日期倒序排序，标记每条记录的排名
            ROW_NUMBER() OVER (PARTITION BY id, source ORDER BY date DESC) AS rn
        FROM delivery_service
    ) ranked_ds
    WHERE rn = 1 -- 只保留每组第一条（最新记录）
) ds ON 
    ed.id = ds.id 
    -- 通过CASE语句建立event和source的映射关系
    AND ds.source = CASE ed.event 
        WHEN 'checkout' THEN 'makeBasket' 
        WHEN 'buildOrder' THEN 'buildPurchase' 
    END;

逻辑说明 #

1. 筛选最新记录：
   内层子查询用ROW_NUMBER()窗口函数，按id和source分组，每组内按date倒序排序，排名为1的就是该组最新的记录。
2. 关联映射：
   使用LEFT JOIN确保estimate_details的所有数据都被保留，通过CASE语句将event值转换为对应的source值，实现两张表的关联匹配。

替代方案（仅需日期字段时） #

如果只需要关联最新日期，不需要delivery_service的其他字段，可简化为：

SELECT 
    ed.id,
    ed.event,
    ds.date AS `Date`,
    ed.`...`
FROM estimate_details ed
LEFT JOIN (
    SELECT 
        id,
        source,
        MAX(date) AS date
    FROM delivery_service
    GROUP BY id, source
) ds ON 
    ed.id = ds.id 
    AND ds.source = CASE ed.event 
        WHEN 'checkout' THEN 'makeBasket' 
        WHEN 'buildOrder' THEN 'buildPurchase' 
    END;

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