PostgreSQL中关联表时基于最新记录筛选的JOIN查询问题

我有companies和transcripts两张表，二者为多对一关联（一个公司对应多条转录本记录）。需要筛选出同时满足以下两个条件的公司ID：

1. company.last_checked_at早于当前日期1周
2. 该公司关联的最新transcript.published_at早于当前日期3个月

目前的关联查询无法仅限定最新的转录本记录，导致误返回那些存在旧转录本但也有近期转录本的公司。

公司表（companies） #

转录本表（transcripts） #

预期结果 #

• 不包含ACME：其last_checked_at在7天内
• 不包含MeepMeep：虽last_checked_at超7天，但最新转录本在3个月内
• 包含TNT：last_checked_at超7天且最新转录本超3个月

尝试过的SQL语句 #

SELECT * FROM summaries s LEFT OUTER JOIN companies c ON s.company = c.id WHERE s.published_at < now() - INTERVAL '3 months' ORDER BY s.published_at ASC limit 1

解决方案 #

要解决这个问题，核心是先获取每个公司的最新转录本发布时间，再和公司表关联筛选条件。可以用子查询分组获取最新时间，再进行关联查询：

SELECT c.id
FROM companies c
INNER JOIN (
    -- 子查询：按公司分组，获取每个公司的最新转录本发布时间
    SELECT company, MAX(published_at) AS latest_published
    FROM transcripts
    GROUP BY company
) t ON c.id = t.company
WHERE 
    -- 条件1：last_checked_at早于当前1周
    c.last_checked_at < NOW() - INTERVAL '1 week'
    -- 条件2：最新转录本发布时间早于当前3个月
    AND t.latest_published < NOW() - INTERVAL '3 months';

逻辑说明 #

1. 子查询通过GROUP BY company和MAX(published_at)，精准拿到每个公司对应的最新转录本发布时间，避免旧记录干扰。
2. 将子查询结果与公司表关联，同时验证两个时间条件，确保只返回完全符合要求的公司ID。

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