多表查询：如何获取各里程碑日期对应的前置最新Value值？

需要从两张表中查询每个里程碑对应的目标值，具体需求：

• Start/Finish里程碑：直接取对应日期的Value
• 里程碑1/2：取该里程碑日期之前**最新（日期最大）**的记录的Value

源表数据 #

表1（值记录表）

表2（里程碑表）

期望结果 #

解决方案 #

之前用min()函数的思路不对，因为需求是取日期最新的记录，和Value本身的大小无关，必须通过排序日期来筛选目标记录。下面提供两种可行的SQL写法：

方法1：窗口函数（推荐，大数据量性能更优）

SELECT 
    sub.ID,
    sub.Value,
    sub.MileStone
FROM (
    SELECT 
        t2.ID,
        t2.MileStone,
        t1.Value,
        ROW_NUMBER() OVER (
            PARTITION BY t2.ID, t2.MileStone 
            ORDER BY t1.DATE DESC
        ) AS rn
    FROM 表2 t2
    JOIN 表1 t1 ON t1.ID = t2.ID AND t1.DATE <= t2.DATE
) AS sub
WHERE rn = 1
ORDER BY sub.ID, 
         CASE sub.MileStone 
             WHEN 'Start' THEN 1 
             WHEN '1' THEN 2 
             WHEN '2' THEN 3 
             WHEN 'Finish' THEN 4 
         END;

逻辑说明：

1. 先将两张表按ID关联，只保留值记录表中日期≤里程碑日期的记录
2. 用ROW_NUMBER()窗口函数按「ID+里程碑」分组，每组内按值记录日期降序排序，最新的记录会被标记为rn=1
3. 外层筛选rn=1的记录，就是每个里程碑对应的目标值
4. 最后用CASE语句保证结果按Start→1→2→Finish的顺序排列

方法2：关联子查询（写法简洁）

SELECT 
    t2.ID,
    (SELECT t1.Value 
     FROM 表1 t1 
     WHERE t1.ID = t2.ID AND t1.DATE <= t2.DATE 
     ORDER BY t1.DATE DESC 
     LIMIT 1) AS Value,
    t2.MileStone
FROM 表2 t2
ORDER BY t2.ID, 
         CASE t2.MileStone 
             WHEN 'Start' THEN 1 
             WHEN '1' THEN 2 
             WHEN '2' THEN 3 
             WHEN 'Finish' THEN 4 
         END;

逻辑说明：
对表2的每一行，通过子查询找到对应ID且日期≤里程碑日期的记录，按日期降序取第一条的Value，直接得到目标结果。

内容的提问来源于stack exchange，提问作者A. Romain