PostgreSQL关联两张表时如何查询各项目对应的最新更新日期与进度

错误原因分析 #

• 第一种写法：将progress加入了GROUP BY分组字段，同一个项目下不同进度值会被拆分为多个分组，自然会返回多条同一project_id的结果。
• 第二种写法：首先缺少两张表的project_id关联条件，会产生笛卡尔积导致重复；其次使用rank()窗口函数，若同一个项目同一天存在多条更新记录，所有同日期的记录rank值都为1，也会返回多条结果。

正确查询方案 #

方案1：使用PostgreSQL专属DISTINCT ON（性能更优）

适合直接按分组取排序后第一条的场景，写法更简洁：

SELECT DISTINCT ON (tpd.project_id)
    tpd.*,
    tpu.date AS latest_update_date,
    tpu.progress AS latest_progress
FROM company.transition_project_details tpd
LEFT JOIN company.transition_project_updates tpu 
    ON tpd.project_id = tpu.project_id 
    AND tpu.company_id = 2
WHERE tpd.company_transition_id = 2
ORDER BY tpd.project_id, tpu.date DESC;

说明：DISTINCT ON (tpd.project_id)会按项目ID去重，仅保留每个项目排序后的第一条记录，按日期倒序排序后第一条就是最新更新。如果不需要保留无更新记录的项目，把LEFT JOIN改成INNER JOIN即可。

方案2：使用窗口函数（兼容性更强）

如果需要更灵活的排序规则，或者要兼容其他SQL语法，可以用ROW_NUMBER代替RANK避免同排名重复：

SELECT 
    tpd.*,
    project_updates.latest_update_date,
    project_updates.latest_progress
FROM company.transition_project_details tpd
LEFT JOIN (
    SELECT 
        project_id,
        date AS latest_update_date,
        progress AS latest_progress,
        ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY date DESC, progress DESC) AS rn
    FROM company.transition_project_updates 
    WHERE company_id = 2
) project_updates 
ON tpd.project_id = project_updates.project_id 
AND project_updates.rn = 1
WHERE tpd.company_transition_id = 2;

说明：ROW_NUMBER会给每个分区内的记录生成唯一的行号，就算同一天有多条更新，也只会返回排序后的第一条，后面加progress DESC是自定义同日期的优先级规则，可根据需求调整。

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