PostgreSQL条件关联查询：保留与目标日期最接近的记录

问题分析 #

你原查询因database.mods表中同一goal_id存在多条修改记录，导致user_steps每条记录关联多条mod数据出现重复。你尝试的查询未返回mod数据，核心问题有两点：

1. 子查询未关联goal_id，是全局查找最大修改时间，而非同一目标下的记录；
2. 用us.created_datetime = (select max(...))要求修改时间与创建时间完全相等，几乎无法匹配到数据。

解决方案 #

方案1：用LATERAL JOIN匹配最接近的记录（推荐） #

PostgreSQL的LATERAL JOIN可针对每条user_steps记录，精准查询同一goal_id下修改时间最接近其创建时间的mod记录：

SELECT 
    us.id AS user_step_id,
    us.goal_id,
    us.user_id,
    us.created_datetime,
    m.mod_code,
    m.modified_datetime
FROM database.user_steps us
LEFT JOIN LATERAL (
    SELECT mod_code, modified_datetime
    FROM database.mods m
    WHERE m.goal_id = us.goal_id
    -- 按时间差绝对值排序，取最接近的第一条
    ORDER BY ABS(EXTRACT(EPOCH FROM (us.created_datetime - m.modified_datetime))) ASC
    LIMIT 1
) m ON true
WHERE us.user_id = 'USv3s_tggf19';

方案2：仅匹配早于创建时间的最新记录 #

如果只需要早于或等于created_datetime的最新修改记录，调整排序逻辑即可：

SELECT 
    us.id AS user_step_id,
    us.goal_id,
    us.user_id,
    us.created_datetime,
    m.mod_code,
    m.modified_datetime
FROM database.user_steps us
LEFT JOIN LATERAL (
    SELECT mod_code, modified_datetime
    FROM database.mods m
    WHERE m.goal_id = us.goal_id
      AND m.modified_datetime <= us.created_datetime
    -- 按修改时间倒序，取最新的一条
    ORDER BY m.modified_datetime DESC
    LIMIT 1
) m ON true
WHERE us.user_id = 'USv3s_tggf19';

方案3：用窗口函数标记唯一记录 #

通过窗口函数给同一goal_id下的mod记录按时间差排序，再筛选出第一条有效记录：

WITH ranked_mods AS (
    SELECT 
        m.goal_id,
        m.mod_code,
        m.modified_datetime,
        us.id AS user_step_id,
        ROW_NUMBER() OVER (
            PARTITION BY us.id, m.goal_id 
            ORDER BY ABS(EXTRACT(EPOCH FROM (us.created_datetime - m.modified_datetime))) ASC
        ) AS rn
    FROM database.user_steps us
    LEFT JOIN database.mods m ON us.goal_id = m.goal_id
    WHERE us.user_id = 'USv3s_tggf19'
)
SELECT 
    us.id AS user_step_id,
    us.goal_id,
    us.user_id,
    us.created_datetime,
    rm.mod_code,
    rm.modified_datetime
FROM database.user_steps us
LEFT JOIN ranked_mods rm ON us.id = rm.user_step_id AND rm.rn = 1
WHERE us.user_id = 'USv3s_tggf19';

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