按ID与日期匹配两表并取最新记录的SQL能否简化？

当然能简化！先咱们把你的业务需求再捋一遍，确保方向没偏：

• 从表table1筛选name='A'的记录，按dt分组取每组load_id最大的id（比如2023-01-05要保留load_id=3的那条）
• 从表table2按dt、param分组取每组load_id最大的对应id记录（2023-01-05保留load_id=13的那条）
• 若table2的日期在table1中不存在，要保留table2匹配最后已知id的记录（原SQL其实没处理这个逻辑，后面咱们补上）

原SQL的问题 #

原SQL嵌套了两层子查询，可读性较差；而且LEFT JOIN仅匹配同日期的id，当table2的dt不在table1中时，返回的id会是NULL，没有满足第三个需求。

简化后的SQL方案 #

用CTE（公共表表达式）分层处理逻辑，同时补全需求3的“最后已知id”匹配：

WITH t1_valid AS (
    -- 需求1：获取table1中每个dt下name='A'且load_id最大的id
    SELECT dt, id
    FROM (
        SELECT 
            dt, id, load_id,
            ROW_NUMBER() OVER (PARTITION BY dt ORDER BY load_id DESC) AS rn
        FROM table1
        WHERE name = 'A'
    ) sub
    WHERE rn = 1
),
t2_valid AS (
    -- 需求2：获取table2中每个dt+param下load_id最大的记录
    SELECT dt, param, load_id, id
    FROM (
        SELECT 
            dt, param, load_id, id,
            ROW_NUMBER() OVER (PARTITION BY dt, param ORDER BY load_id DESC) AS rn
        FROM table2
    ) sub
    WHERE rn = 1
)
-- 关联并处理需求3：T2日期不在T1时，取T1中最近的历史id
SELECT 
    t2.dt,
    t2.param,
    t2.load_id,
    -- 优先用同日期的T1 id，没有则取T1中<=当前T2日期的最近id
    COALESCE(
        t1.id,
        (SELECT id FROM t1_valid WHERE dt <= t2.dt ORDER BY dt DESC LIMIT 1)
    ) AS id
FROM t2_valid t2
LEFT JOIN t1_valid t1 ON t1.dt = t2.dt
ORDER BY t2.dt, t2.param;

简化点说明 #

1. 可读性提升：用CTE把table1和table2的核心逻辑拆分，每一步的职责清晰，比嵌套子查询更容易维护
2. 逻辑更简洁：用ROW_NUMBER()窗口函数一步过滤出每组的最大load_id记录，替代原SQL“先算max再过滤”的两步操作
3. 满足需求3：通过COALESCE()结合子查询，实现了“T2日期不在T1时取最后已知id”的逻辑，填补了原SQL的缺失

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