如何关联comment取最早已审核记录更新post表date字段

原有SQL存在两个核心错误：

• 关联逻辑错误：原SQL写的p2.id = c.id是将post主键与comment表主键关联，正确关联逻辑应为comment的post_id字段关联post主键，直接导致无有效评论的post被错误更新
• 未做日期聚合：没有对同post下的有效评论做最早日期提取，数据库遇到同post匹配多条评论时，会按存储顺序随机取值，导致取到错误的日期值

按照不使用低性能相关子查询的要求，使用聚合结果集关联的写法，性能可满足大数据集场景：

UPDATE post p
SET date = c.min_valid_date
FROM (
    SELECT post_id, MIN(date) AS min_valid_date
    FROM comment
    WHERE checked = true
    GROUP BY post_id
) c
WHERE p.id = c.post_id
  AND p.date IS NULL;

逻辑说明 #

• 关联的comment侧先做批量聚合：仅筛选checked = true的有效评论，按post_id分组后用MIN(date)直接计算每个post对应最早的有效评论日期，该步骤仅需扫描一次comment表，不会逐行匹配post表
• 采用INNER JOIN关联聚合结果与post表，天然过滤掉无有效评论的post记录：测试数据中id=2、3的post在聚合结果中无匹配的post_id，不会进入更新范围，保持原有null值不变
• 增加p.date IS NULL条件，避免覆盖post表中已有date值的记录

性能优化建议 #

针对大数据集场景，可提前建索引避免全表扫描：

• 给comment表建联合索引：(checked, post_id, date)，聚合计算时可直接走索引完成，无需回表
• 给post表建联合索引：(id, date)，关联匹配和条件过滤时可直接走索引

测试数据验证 #

对给出的测试数据执行该SQL后：

• 聚合comment有效评论仅返回1条结果：post_id=1，min_valid_date=2020-01-01
• 仅id=1的post被匹配更新，date字段设为2020-01-01
• id=2、3的post无匹配聚合结果，date保持null，完全符合预期结果

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